How to Match Two Excel Ranges and Return Corresponding Values
Question details
The user needs to compare two ranges based on two-column keys, identify the matching records, and append the corresponding values to the original dataset using dynamic array formulas.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Merging and matching related data from two different tables into a single cohesive dataset based on specific lookup keys.
- Observed behavior
- The user requires an automated array formula solution to retrieve and append values rather than performing manual lookups or duplicating data entry.
Ensure you are using a modern version of your spreadsheet software that supports dynamic array functions (like LET, XMATCH, and HSTACK), and verify that your lookup columns do not contain hidden trailing spaces.
Use LET, XMATCH, and INDEX (Dynamic Array)
This is the most efficient method for modern spreadsheet versions. It uses a dynamic array formula to look up matches and append the results without the need to copy the formula down.
By utilizing the LET function, you can define variables within your formula to make it cleaner. XMATCH locates the position of the matching key, and INDEX retrieves the corresponding row values.
Click on the top-left cell of the blank range where you want the matched and merged dataset to appear.
Type the formula combining LET, XMATCH, and INDEX. For example: =LET(rowId, XMATCH(B1:B10, E1:E5), HSTACK(EXPAND(A1:B10, , 3, ""), IF(ISNUMBER(rowId), INDEX(D1:E5, rowId, {1,2}), "")))
Press Enter. The formula will automatically calculate and spill the matched results into the adjacent rows and columns, bringing the corresponding values from columns D and E where column B matches column E.
Match Multi-Column Keys using REDUCE and VSTACK
Use this advanced array combination when you need to match rows based on multiple columns concatenated together.
Use Helper Columns and Traditional Lookup
Ideal for users on older spreadsheet versions that do not support dynamic arrays or newer functions like LET and XMATCH.
Easily Match and Merge Data Using WPS Spreadsheet
WPS Office Spreadsheet provides comprehensive support for modern dynamic array functions, making complex data matching and multi-column lookups effortless. You can quickly analyze data, write powerful formulas, and manage large datasets seamlessly without worrying about version limitations.
- 1. Open your dataset: Launch WPS Spreadsheet and open the workbook containing your data ranges.
- 2. Select the target cell: Click on the cell where you want the matched data array to begin.
- 3. Enter your array formula: Type in your preferred lookup formula, such as XLOOKUP or a LET/XMATCH combination.
- 4. Spill the results: Press Enter to instantly populate the matching rows and corresponding values across your sheet.
Frequently Asked Questions
Why is my dynamic array formula returning a #SPILL! error?
A #SPILL! error occurs when the array formula requires multiple cells to display its matched results, but one or more of the required output cells already contain data or spaces. Clear the destination range to allow the formula to expand.
Can I use XLOOKUP instead of XMATCH and INDEX for this?
Yes. For straightforward range matching, XLOOKUP is an excellent alternative that returns an array of values directly. You can use a formula like =XLOOKUP(B1:B10, E1:E5, D1:E5, "Not Found").
Are functions like HSTACK and VSTACK available in older spreadsheet versions?
No, HSTACK, VSTACK, LET, and XMATCH are modern dynamic array functions. If you are using an older version without Microsoft 365 or a recent version of WPS Office, you must rely on traditional helper columns and VLOOKUP.
How do I match multiple criteria without concatenating columns?
You can perform multi-criteria lookups using boolean logic within array formulas. By multiplying arrays together (e.g., (A1:A10=D1)*(B1:B10=E1)), you create an array of 1s and 0s that functions like FILTER or XLOOKUP can evaluate to find precise matches.
