SpreadsheetFormulas
Advanced Lookups Last updated: 2026-08-20

How to INDEX MATCH with Multiple Criteria in Excel & Google Sheets

Lookup values across multiple criteria using INDEX MATCH array formulas for universal compatibility in all versions of Excel.

Quick Answer & Formula
Excel Sheets Intermediate
=INDEX(return_range, MATCH(1, (criteria1_range = val1) * (criteria2_range = val2), 0))

To lookup with multiple conditions in any version of Excel: =INDEX(Return_Col, MATCH(1, (ColA = "Sales") * (ColB = "Manager"), 0)). In legacy Excel, press Ctrl+Shift+Enter.

How to INDEX MATCH with Multiple Criteria in Excel & Google Sheets

For workbooks that must remain compatible with older versions of Excel where XLOOKUP is unavailable, INDEX MATCH with boolean multiplication is the industry standard.

1. The Core Formula

=INDEX(C2:C100, MATCH(1, (A2:A100 = "Engineering") * (B2:B100 = "Senior"), 0))

2. Step-by-Step Execution

  1. (A2:A100 = "Engineering") creates an array of {TRUE; FALSE; TRUE...}.
  2. (B2:B100 = "Senior") creates an array of {FALSE; FALSE; TRUE...}.
  3. Multiplying them creates an array of {0; 0; 1...}.
  4. MATCH(1, ..., 0) locates the exact row containing the number 1.
  5. INDEX returns the value from that matching row.
?

Frequently Asked Questions

Do I still need to press Ctrl+Shift+Enter for multi-condition INDEX MATCH?

In modern Excel 365, Excel 2021, and Google Sheets, you can press regular Enter. In Excel 2019 and older, you must press Ctrl+Shift+Enter to execute as an array formula.