How to use INDEX MATCH MATCH with multiple criteria in Excel
Excel formula
=INDEX(C2:E7,MATCH(1,(A2:A7=H1)*(B2:B7=H2),0),MATCH(H3,C1:E1,0))Find the row with MATCH(1,(range1=crit1)*(range2=crit2),0) and the column with a normal MATCH, then let INDEX return the crossing cell.
- Result on the example
- 112 in
H4 - Works in
- Every current version
- Google Sheets
- Different formula, see below
See it work
The formula is already in H4. Change any input and the result updates, or edit the formula to experiment.
How it works
The row needs two keys, so the row MATCH searches for 1 in (A2:A7=H1)*(B2:B7=H2): the multiplied tests are 1 only on the row where both the region and the product match (West Monitors, the 4th row). The column MATCH finds Feb in C1:E1. INDEX returns the cell where they cross.
Google Sheets needs ARRAYFORMULA around it so the tests are worked out for every row.
=INDEX(C2:E7,MATCH(1,(A2:A7=H1)*(B2:B7=H2),0),MATCH(H3,C1:E1,0))Result112
C2:E76 rows × 3 cols: 142, 155, 168, 96, …A range: 6 rows × 3 columns.
A2:A76 rows × 1 col: "East", "East", "West", "West", …A range: 6 cells in one column.
H1"West"The value in H1.
A2:A7=H1{6 rows × 1 col} FALSE, FALSE, TRUE, TRUE, …The = operator tests whether the two sides are equal.
B2:B76 rows × 1 col: "Laptops", "Monitors", "Laptops", "Monitors", …A range: 6 cells in one column.
H2"Monitors"The value in H2.
B2:B7=H2{6 rows × 1 col} FALSE, TRUE, FALSE, TRUE, …The = operator tests whether the two sides are equal.
A2:A7=H1)*(B2:B7=H2{6 rows × 1 col} 0, 0, 0, 1, …The * operator multiplies.
MATCH(1,(A2:A7=H1)*(B2:B7=H2),0)4MATCH: Position of a value in a list. 0 = exact match.- lookup_value
1 - lookup_array
A2:A7=H1)*(B2:B7=H2{6 rows × 1 col} 0, 0, 0, 1, … - match_type
0
- lookup_value
H3"Feb"The value in H3.
C1:E11 row × 3 cols: "Jan", "Feb", "Mar"A range: 3 cells in one row.
MATCH(H3,C1:E1,0)2MATCH: Position of a value in a list. 0 = exact match.- lookup_value
H3"Feb" - lookup_array
C1:E11 row × 3 cols: "Jan", "Feb", "Mar" - match_type
0
- lookup_value
INDEX(C2:E7,MATCH(1,(A2:A7=H1)*(B2:B7=H2),0),MATCH(H3,C1:E1,0))112INDEX: Value at a given row and column of a range.- array
C2:E76 rows × 3 cols: 142, 155, 168, 96, … - row_num
MATCH(1,(A2:A7=H1)*(B2:B7=H2),0)4 - column_num
MATCH(H3,C1:E1,0)2
- array
In Google Sheets
Google Sheets needs a different formula: =ARRAYFORMULA(INDEX(C2:E7,MATCH(1,(A2:A7=H1)*(B2:B7=H2),0),MATCH(H3,C1:E1,0))). On the same data it gives the same result. Here it is running in Google Sheets mode.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =XLOOKUP(H3,C1:E1,XLOOKUP(1,(A2:A7=H1)*(B2:B7=H2),C2:E7)) | 112 | The inner XLOOKUP returns the matching row, the outer one picks the month from it. Excel 2021 and Microsoft 365. |
| =SUMPRODUCT((A2:A7=H1)*(B2:B7=H2)*(C1:E1=H3)*C2:E7) | 112 | Works in every version without Ctrl+Shift+Enter, for numbers. If a combination appears twice, it adds them. |
Common mistakes
- In Excel 2019 and earlier, confirm the INDEX/MATCH version with Ctrl+Shift+Enter, or it returns #N/A.
- Put each test in its own brackets before multiplying.
A2:A7=H1*B2:B7=H2multiplies first and compares nonsense. - If two rows share both keys, MATCH returns the first one.
Try it yourself
Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.
Use INDEX MATCH MATCH with multiple criteria
H4, return the units for the region in H1, the product in H2 and the month in H3.Questions
How do I add a third row criterion?
Multiply one more test: (A2:A7=H1)*(B2:B7=H2)*(C2:C7=H5).
Can the column side have two criteria too?
Yes. With two header rows, multiply a test on each: MATCH(1,(C1:E1=H3)*(C2:E2=H5),0), with the data starting in row 3.
Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.