Use INDEX and MATCH in Excel to Find a Cell with Multiple Criteria
Question details
Find and return the value of a cell where two column-header criteria and one row criterion intersect.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A worksheet contains criteria such as X, 2, and 300 in specific header rows and a lookup column. The user wants a formula that locates the corresponding intersection.
- Observed behavior
- The desired result is the value from the matching cell, rather than just its row number, column number, or visual location.
Check that your two header ranges cover the same columns as the result range and that your row lookup range covers the same rows. Enter the criteria in separate cells and make sure numeric criteria are stored consistently as numbers or text.
Return the intersecting value with INDEX and two MATCH functions
Use this formula when two header criteria identify a column and a third criterion identifies a row.
In this example, the lookup table is A1:D4, its two header rows are A1:D1 and A2:D2, and its row lookup values are in C1:C4. F2 and F3 hold the header criteria, while F5 holds the row criterion.
The first MATCH locates the row. The second MATCH compares the combined header criteria across the columns. INDEX returns the value at the resulting row and column.
In the worksheet, enter X in F2, 2 in F3, and 300 in F5, or replace these values with your actual criteria.
Select an empty result cell outside A1:D4, such as F7, and enter =INDEX(A1:D4,MATCH(F5,C1:C4,0),MATCH(F2&F3,INDEX(A1:D1&A2:D2,),0)). Replace the ranges if your table is in a different location.
Press Enter and verify that the returned value lies in the row where column C matches F5 and the column whose first two headers match F2 and F3. In older Excel versions, if normal entry does not evaluate the array expression correctly, edit the formula and confirm it with Ctrl+Shift+Enter.
Highlight the intersecting cell with conditional formatting
Use this option if identifying the cell means coloring it in the table rather than returning its value elsewhere.
Find the Matching Intersection in WPS Spreadsheets
Use INDEX and MATCH in WPS Spreadsheets to locate a row and a column from separate criteria, then return the intersecting value from your Excel workbook.
- 1. Open the workbook: Launch WPS Spreadsheets and choose Open to load your .xlsx or .xls workbook.
- 2. Set up the criteria: For a table in A1:D4 with headers in rows 1 and 2 and row lookup values in column C, enter your header criteria in F2 and F3 and your row criterion in F5.
- 3. Enter the intersection formula: Select F7 and enter =INDEX(A1:D4,MATCH(F5,C1:C4,0),MATCH(F2&F3,INDEX(A1:D1&A2:D2,),0)). Adjust the ranges to match your worksheet.
- 4. Check the returned value: Press Enter and compare the result with the matching row and header pair. If your WPS version requires array entry for the combined header expression, edit the formula and confirm it with Ctrl+Shift+Enter.
Frequently Asked Questions
How do INDEX and MATCH handle three criteria in this formula?
MATCH(F5,C1:C4,0) finds the row using one criterion. The other MATCH combines F2 and F3 and compares them with the combined values in header rows 1 and 2 to find the column. INDEX then returns the value at that intersection.
Why does the formula return #N/A even though the value appears in the table?
One of the MATCH functions cannot find an exact match. Check F2, F3, and F5 for extra spaces and number-versus-text differences. You can test =MATCH(F5,C1:C4,0) in a blank cell to determine whether the row criterion is the problem.
Can I use this formula with a larger table?
Yes. For a table in A1:H100 with two header rows and row lookup values in column C, use =INDEX(A3:H100,MATCH(F5,C3:C100,0),MATCH(F2&F3,INDEX(A1:H1&A2:H2,),0)). The result range and row lookup range must start and end on the same rows, and the header ranges must cover the same columns as the result range.
What happens when more than one row or column matches?
MATCH returns the first match in each lookup range, so the formula returns only one intersection. If you want to see all matching intersections, select the data area and create a formula-based rule through Home > Conditional Formatting > New Rule.
Do I need Ctrl+Shift+Enter for the combined-header formula?
Current Microsoft 365 versions generally accept this formula with Enter. Older Excel versions may require Ctrl+Shift+Enter because the header comparison uses an array expression. Do not type the surrounding array braces manually.
