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

Need it for your exact data?

Describe your columns in plain English and get the precise formula for your sheet, with the right Excel or Sheets syntax.

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

INDEXMATCHSUMIFS

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

Last reviewed: