Match Two Columns Between Excel Sheets and Return a Value
Question details
The user needs a method to match records using two distinct criteria from one worksheet against two columns in another worksheet, and then return the corresponding value from a third column.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Looking up and returning specific data across multiple sheets when a single-criteria VLOOKUP is insufficient to uniquely identify the row.
- Observed behavior
- The user requires an alternative formula to evaluate two conditions simultaneously across different columns before retrieving the target value.
Ensure both worksheets are open and clearly identify the exact column letters containing your two criteria and the values you wish to return.
Use the XLOOKUP Function (Recommended for Newer Versions)
XLOOKUP allows you to easily combine multiple criteria using the ampersand (&) operator without needing complex array formulas.
If you are using a modern spreadsheet application, XLOOKUP is the most efficient way to match multiple criteria. By concatenating your lookup values and your lookup arrays, you can evaluate two or more columns simultaneously.
Click on the cell in Spreadsheet 1 where you want the returned value to appear (e.g., C2).
Type the formula: =XLOOKUP(A2&B2,'Spreadsheet 2'!$A$2:$A$1000&'Spreadsheet 2'!$B$2:$B$1000,'Spreadsheet 2'!$C$2:$C$1000,"")
Press Enter to apply the formula. You can then click and drag the fill handle down to apply this logic to the remaining rows in your column.
Use INDEX and MATCH with Multiple Criteria
For older spreadsheet versions that do not support XLOOKUP, a combination of INDEX, MATCH, and IFERROR is the standard alternative.
Match Data Across Sheets Effortlessly in WPS Spreadsheet
WPS Spreadsheet fully supports advanced lookup functions, including XLOOKUP and complex array-based INDEX/MATCH combinations, allowing you to cross-reference data accurately and efficiently.
- 1. Open your workbook in WPS: Launch WPS Spreadsheet and open the file containing your multiple worksheets.
- 2. Navigate to the return cell: Select the empty cell where you want the matched data to be displayed.
- 3. Enter the formula: Type in your XLOOKUP or INDEX/MATCH formula exactly as you would in standard Excel.
- 4. Retrieve the data: Press Enter to instantly fetch the corresponding value from your second sheet.
Frequently Asked Questions
Why does my INDEX and MATCH formula return a #VALUE! error?
This usually happens in older spreadsheet versions if you don't evaluate the function as an array formula. Try double-clicking the cell and pressing Ctrl+Shift+Enter instead of just Enter. Additionally, verify that your lookup array ranges (like A2:A1000 and B2:B1000) are exactly the same size.
Can I use standard VLOOKUP to match two columns?
Standard VLOOKUP only supports a single lookup value. To use VLOOKUP for multiple criteria, you must create a new 'helper column' in your source data that concatenates the two criteria (e.g., =A2&B2), and then use that combined column as your primary lookup value.
How do I handle #N/A errors when a match isn't found?
If using INDEX and MATCH, wrap your entire formula in the IFERROR function, like =IFERROR(your_formula, "Not Found"). If you are using XLOOKUP, it handles this natively through its built-in 'if_not_found' argument, which is the fourth parameter in the formula.
