# How to use INDEX MATCH with multiple criteria in Google Sheets

Wrap the formula in ARRAYFORMULA so the tests run on every row: `=ARRAYFORMULA(INDEX(D2:D9,MATCH(1,(A2:A9=G1)*(B2:B9=I1),0)))`.

**Google Sheets:** `=ARRAYFORMULA(INDEX(D2:D9,MATCH(1,(A2:A9=G1)*(B2:B9=I1),0)))`

**Excel:** In Excel 365 the same INDEX/MATCH works without ARRAYFORMULA; in Excel 2019 and earlier, confirm it with Ctrl+Shift+Enter. `=XLOOKUP(1,(A2:A9=G1)*(B2:B9=I1),D2:D9)` is shorter.

## Example

Sample data (row 1 is the header):

| A | B | C | D | E | F | G | H | I |
| --- | --- | --- | --- | --- | --- | --- | --- | --- |
| Teacher | Period | Subject | Room |  | Teacher | Ms. Alvarez | Period | 3 |
| Mr. Chen | 1 | Algebra | B12 |  | Room |  |  |  |
| Ms. Alvarez | 1 | Biology | S04 |  |  |  |  |  |
| Mr. Chen | 2 | Geometry | B12 |  |  |  |  |  |
| Ms. Alvarez | 2 | Chemistry | S06 |  |  |  |  |  |
| Mrs. Okoro | 1 | English 9 | A21 |  |  |  |  |  |
| Ms. Alvarez | 3 | Biology | S02 |  |  |  |  |  |
| Mrs. Okoro | 3 | English 10 | A23 |  |  |  |  |  |
| Mr. Chen | 3 | Statistics | B14 |  |  |  |  |  |

Formula in G2: `=ARRAYFORMULA(INDEX(D2:D9,MATCH(1,(A2:A9=G1)*(B2:B9=I1),0)))`

Result: S02

## How it works

`(A2:A9=G1)*(B2:B9=I1)` gives 1 on the row where both the teacher and the period match and 0 elsewhere; MATCH finds the 1 and INDEX returns the room on that row. In Google Sheets, a comparison of a whole range only runs row by row inside ARRAYFORMULA. Without it, MATCH sees a single value and returns #N/A.

## Other ways

- `=FILTER(D2:D9,A2:A9=G1,B2:B9=I1)`: Google Sheets' FILTER takes each condition as a separate argument and returns every match.
- `=XLOOKUP(1,(A2:A9=G1)*(B2:B9=I1),D2:D9)`: XLOOKUP handles the tests without ARRAYFORMULA.

## Common mistakes

- Pressing Ctrl+Shift+Enter in Google Sheets adds the ARRAYFORMULA for you.
- Number criteria must be numbers: a period typed as text "3" doesn't equal 3.
- If two rows match both criteria, MATCH returns the first.

## Questions

**How do I add a third criterion?**

Multiply one more test: `*(C2:C9="Biology")`.

**Can I use QUERY instead?**

Yes: `=QUERY(A2:D9,"select D where A = '"&G1&"' and B = "&I1,0)`.

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-multiple-criteria-google-sheets