How to Use Excel INDEX MATCH Formula for a Two-Criteria Matrix Lookup
Question details
The user needs to retrieve a specific value from an Excel matrix at the exact intersection of two lookup criteria, such as a specific year and a color.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Looking up a data point in a two-dimensional matrix using multiple conditions across headers and sub-headers.
- Observed behavior
- A reliable formula is required to locate the column where both criteria perfectly match and return the corresponding value from the target row.
Ensure your data ranges are identically sized and your matrix headers do not contain merged cells, as merged cells can prevent array calculations from working correctly.
Use Array-Entered INDEX and MATCH with Multiple Criteria
This method uses boolean logic arrays multiplied together to find the exact column where both conditions are met.
By multiplying two conditions together, such as `(Range1=Condition1)*(Range2=Condition2)`, Excel creates an array of 1s and 0s. The MATCH function is then instructed to find the number 1, which represents the exact column where both criteria are true.
Click on the cell where you want the lookup result to be displayed.
Type the formula `=IFERROR(INDEX(Sheet1!$A$4:$E$4,MATCH(1,(Sheet1!$A$1:$E$1=B$1)*(Sheet1!$A$2:$E$2=$A2),0)),"")`, replacing the sheet names, lookup range (`$A$4:$E$4`), and criteria with your specific data references.
If you are using an older version of Excel (prior to Microsoft 365 or Office 2021), you must confirm the formula by pressing Ctrl+Shift+Enter. In newer versions, simply press Enter.
Drag the fill handle to copy the formula across other columns or rows as needed.
Use a Nested INDEX inside MATCH to Avoid Array Entry
This alternative wraps the criteria array inside an additional INDEX function, forcing older versions of Excel to process the array without requiring Ctrl+Shift+Enter.
Perform Advanced Matrix Lookups Effortlessly in WPS Spreadsheet
WPS Spreadsheet fully supports advanced array formulas, dynamic arrays, and nested functions like INDEX and MATCH, empowering you to process complex multi-criteria data lookups with ease.
- 1. Open your workbook: Launch WPS Spreadsheet and open your Excel workbook containing the matrix data.
- 2. Apply the lookup formula: Select your target cell and input the multi-criteria formula `=INDEX(..., MATCH(...))`.
- 3. Execute the calculation: Press Enter (or Ctrl+Shift+Enter for legacy compatibility), and WPS Spreadsheet will instantly calculate and return the precise intersection value.
Frequently Asked Questions
Why does my multi-criteria INDEX MATCH formula return a #VALUE! error?
In versions of Excel older than Microsoft 365, formulas that multiply ranges together to create boolean arrays require special execution. You must press Ctrl+Shift+Enter to evaluate them as array formulas; otherwise, a #VALUE! error is returned.
Can I use XLOOKUP instead of INDEX and MATCH for two criteria?
Yes. If you are using Microsoft 365, Excel 2021, or WPS Spreadsheet, you can nest two XLOOKUP functions to perform a two-dimensional matrix lookup. This method is often easier to read and does not require manual array formula entries.
How do I prevent #N/A errors when the exact lookup match isn't found?
You can wrap your entire INDEX MATCH formula inside an IFERROR function, like this: `=IFERROR(INDEX(...), "")`. This will return a blank cell or a custom message instead of an error code when no exact intersection is found.
Does the order of the criteria in the MATCH function matter?
The order of multiplication inside the MATCH function, such as `(Criteria1)*(Criteria2)`, does not matter due to the commutative property of multiplication. Just ensure that each condition is pointing precisely to its correct lookup array.
