FormulaCraft

How to use INDEX MATCH with two criteria in Excel and Google Sheets

Topic:INDEX / MATCH
Excel & Google Sheets
=INDEX(C2:C5,MATCH(1,(A2:A5=F1)*(B2:B5=F2),0))

Try it with your data

Edit the grid or formula, then run it through a real spreadsheet engine — no signup.

Sample data — click any cell to edit

Runs server-side · free · no signup

Step by step

  1. 1Put the first criterion in F1 (e.g. a region) and the second criterion in F2 (e.g. a product).
  2. 2In the result cell, enter =INDEX(C2:C5,MATCH(1,(A2:A5=F1)*(B2:B5=F2),0)).
  3. 3In Excel (non-365), confirm with Ctrl+Shift+Enter to enter it as an array formula. In Excel 365 and Google Sheets, press Enter normally.
  4. 4Both conditions must be true for a row to match — the multiplication acts as a logical AND.

Tips

Working on a sheet you inherited? Run the Auditor on the whole file first — it flags every #REF!, #N/A, broken column pattern, and inconsistent formula in seconds, free, no signup.

Frequently asked

Why do I need Ctrl+Shift+Enter in older Excel?

The formula evaluates arrays cell-by-cell, which requires explicit array entry in Excel 2019 and earlier. Excel 365 and Google Sheets handle this automatically.

Can I add a third criterion?

Yes — multiply a third Boolean array: (A2:A5=F1)*(B2:B5=F2)*(C2:C5=F3). All three must be TRUE for a match.

More on INDEX / MATCH

See all →
Reference

INDEX / MATCH

A flexible two-function lookup that can return values to the left of the lookup column.

How-to

How to do a two-way lookup

Use INDEX with two MATCH functions to look up a value at the intersection of a matching row and a matching column.

Error fix

Fix #N/A

Fix #N/A errors in INDEX MATCH in Excel and Google Sheets — match_type wrong, arrays different sizes, data-type mismatches, and lookup value absent.

How-to

How to do a two-dimensional lookup

Combine INDEX with two MATCH calls — one for the row and one for the column — to look up a value at the intersection of a row and column header.

Formulas used

INDEXMATCHSUMIFSEXACTISNADSUM

Related tasks

Written and reviewed by FormulaCraft Team. Each formula on this page is run through our verification engine before publishing.

Last reviewed: