Spreadsheet Practice

How to use INDEX MATCH with multiple criteria in Google Sheets

Google Sheets formula

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

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

Result on the example
S02 in G2
Excel
See below

See it work

The formula is already in G2. Change any input and the result updates, or edit the formula to experiment.

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.

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

Result"S02"

  1. D2:D98 rows × 1 col: "B12", "S04", "B12", "S06", …

    A range: 8 cells in one column.

  2. A2:A98 rows × 1 col: "Mr. Chen", "Ms. Alvarez", "Mr. Chen", "Ms. Alvarez", …

    A range: 8 cells in one column.

  3. G1"Ms. Alvarez"

    The value in G1.

  4. A2:A9=G1{8 rows × 1 col} FALSE, TRUE, FALSE, TRUE, …

    The = operator tests whether the two sides are equal.

  5. B2:B98 rows × 1 col: 1, 1, 2, 2, …

    A range: 8 cells in one column.

  6. I13

    The value in I1.

  7. B2:B9=I1{8 rows × 1 col} FALSE, FALSE, FALSE, FALSE, …

    The = operator tests whether the two sides are equal.

  8. A2:A9=G1)*(B2:B9=I1{8 rows × 1 col} 0, 0, 0, 0, …

    The * operator multiplies.

  9. MATCH(1,(A2:A9=G1)*(B2:B9=I1),0)6

    MATCH: Position of a value in a list. 0 = exact match.

    • lookup_value1
    • lookup_arrayA2:A9=G1)*(B2:B9=I1{8 rows × 1 col} 0, 0, 0, 0, …
    • match_type0
  10. INDEX(D2:D9,MATCH(1,(A2:A9=G1)*(B2:B9=I1),0))"S02"

    INDEX: Value at a given row and column of a range.

    • arrayD2:D98 rows × 1 col: "B12", "S04", "B12", "S06", …
    • row_numMATCH(1,(A2:A9=G1)*(B2:B9=I1),0)6
  11. ARRAYFORMULA(INDEX(D2:D9,MATCH(1,(A2:A9=G1)*(B2:B9=I1),0)))"S02"

    ARRAYFORMULA: Runs a formula over whole ranges and returns all the results.

    • array_formulaINDEX(D2:D9,MATCH(1,(A2:A9=G1)*(B2:B9=I1),0))"S02"

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

Other ways to do it

Each one was run on the same example sheet.

FormulaResultWhen to use it
=FILTER(D2:D9,A2:A9=G1,B2:B9=I1)S02Google Sheets' FILTER takes each condition as a separate argument and returns every match.
=XLOOKUP(1,(A2:A9=G1)*(B2:B9=I1),D2:D9)S02XLOOKUP 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.

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 with multiple criteria

In G2, return the room for the teacher in G1 in the period in I1.

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

Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine.