How to Use XLOOKUP vs INDEX MATCH for Multiple-Criteria Lookups in Excel
Question details
The user wants to compare and utilize XLOOKUP and INDEX/MATCH functions to return a specific value based on two or more criteria.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Searching for specific data in a large dataset that requires matching multiple conditions simultaneously.
- Observed behavior
- Retrieving a single matching result from a target column when two or more columns match the specified criteria.
Before applying these formulas, ensure your dataset is organized in a tabular format without merged cells, and that the columns containing your criteria have matching data types and exactly equal lengths.
Use XLOOKUP with Concatenated Search Keys
The simplest method for multiple-criteria lookups is combining the lookup values and lookup arrays using an ampersand (&) and a delimiter.
This approach creates a single, unique lookup string by joining your criteria. It is highly efficient and easier to read than traditional array formulas.
Click on the cell where you want the lookup result to appear.
Type the formula combining your criteria: =XLOOKUP(Criteria1 & "|" & Criteria2, LookupRange1 & "|" & LookupRange2, ReturnRange, "").
Replace the placeholder ranges with your actual cell references, for example: =XLOOKUP(D3&"|"&G3, Data!$D$2:$D$100&"|"&Data!$E$2:$E$100, Data!$H$2:$H$100, "").
Press Enter to apply the formula, then drag the fill handle down to apply it to other rows.
Use INDEX and XMATCH with Boolean Arrays
An alternative and highly robust method using INDEX and XMATCH, which multiplies criteria arrays to find the matching row.
Perform Complex Lookups Seamlessly with WPS Spreadsheet
WPS Spreadsheet fully supports advanced functions like XLOOKUP, XMATCH, and INDEX, allowing you to handle multiple-criteria lookups with ease. It is a lightweight, fully compatible alternative for all your data analysis needs.
- 1. Open WPS Spreadsheet: Launch WPS Office and open your workbook in the Spreadsheet module.
- 2. Apply your formula: Select a cell and input your preferred multiple-criteria XLOOKUP or INDEX/MATCH formula.
- 3. Calculate and analyze: Press Enter to instantly retrieve your data and drag the fill handle to apply it across rows.
Frequently Asked Questions
Why is my XLOOKUP formula returning an error with multiple criteria?
Errors typically occur if the lookup arrays are not the exact same size, or if you forgot to include a delimiter (like '|') when concatenating criteria, causing mismatching text strings.
Which is faster for large datasets: XLOOKUP or INDEX/MATCH?
For extremely large datasets, INDEX/MATCH with Boolean arrays can sometimes be marginally faster or more memory-efficient than concatenating large text strings, though XLOOKUP is generally fast enough for most standard use cases.
Can I use these formulas in older versions of Excel?
XLOOKUP and XMATCH are only available in Microsoft 365, Excel 2021, and modern versions of WPS Office. For older versions, you must use standard INDEX and MATCH combined with array formulas (using Ctrl+Shift+Enter).
