# How to use INDEX MATCH MATCH with multiple criteria in Excel

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.

**Excel:** `=INDEX(C2:E7,MATCH(1,(A2:A7=H1)*(B2:B7=H2),0),MATCH(H3,C1:E1,0))` (Every current version)

**Google Sheets:** `=ARRAYFORMULA(INDEX(C2:E7,MATCH(1,(A2:A7=H1)*(B2:B7=H2),0),MATCH(H3,C1:E1,0)))`

## Example

Sample data (row 1 is the header):

| A | B | C | D | E | F | G | H |
| --- | --- | --- | --- | --- | --- | --- | --- |
| Region | Product | Jan | Feb | Mar |  | Region | West |
| East | Laptops | 142 | 155 | 168 |  | Product | Monitors |
| East | Monitors | 96 | 104 | 99 |  | Month | Feb |
| West | Laptops | 131 | 127 | 149 |  | Units |  |
| West | Monitors | 88 | 112 | 95 |  |  |  |
| North | Laptops | 74 | 81 | 90 |  |  |  |
| North | Monitors | 52 | 49 | 61 |  |  |  |

Formula in H4: `=INDEX(C2:E7,MATCH(1,(A2:A7=H1)*(B2:B7=H2),0),MATCH(H3,C1:E1,0))`

Result: 112

## 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.

## Other ways

- `=XLOOKUP(H3,C1:E1,XLOOKUP(1,(A2:A7=H1)*(B2:B7=H2),C2:E7))`: 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)`: 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=H2` multiplies first and compares nonsense.
- If two rows share both keys, MATCH returns the first one.

## 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.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/index-match-match-multiple-criteria