SheetSense

INDEX MATCH with multiple criteria

INDEX and MATCH can look up a row that must match two columns at once, by multiplying two TRUE/FALSE tests so that only rows meeting both conditions equal 1.

fx
=INDEX($C$2:$C$500,MATCH(1,($A$2:$A$500=F2)*($B$2:$B$500=G2),0))

The sheet: Rates sheet: column A is Region, column B is Product, column C is Unit price. Cell F2 holds the region you want and G2 holds the product.

How it works, step by step

  1. 01($A$2:$A$500=F2) tests every region in the column and returns a list of TRUE and FALSE values.
  2. 02($B$2:$B$500=G2) does the same for the product column.
  3. 03Multiplying the two lists turns TRUE into 1 and FALSE into 0, so a row scores 1 only when region and product both match.
  4. 04MATCH(1,...,0) finds the position of the first 1 in that list. The 0 forces an exact match.
  5. 05INDEX returns the value at that position from the Unit price column.

The same logic in four tools

Excel
=INDEX($C$2:$C$500,MATCH(1,($A$2:$A$500=F2)*($B$2:$B$500=G2),0))
Google Sheets
=ARRAYFORMULA(INDEX($C$2:$C$500,MATCH(1,($A$2:$A$500=F2)*($B$2:$B$500=G2),0)))
SQL
SELECT o.order_id, o.region, o.product, r.unit_price
FROM orders o
LEFT JOIN rates r
  ON r.region = o.region
 AND r.product = o.product;
Python (pandas)
import pandas as pd

result = orders.merge(
    rates[["region", "product", "unit_price"]],
    on=["region", "product"],
    how="left",
)

Mistakes that break it

  • !Google Sheets needs ARRAYFORMULA around this lookup. Without it the two comparisons are evaluated as single values and the result is wrong or an error.
  • !In Excel 2019 and older, the formula must be confirmed with Ctrl+Shift+Enter, otherwise it returns #N/A or #VALUE!.
  • !Every range inside the formula must have the same number of rows. $A$2:$A$500 with $C$2:$C$499 returns the wrong row or an error.
  • !Only the first matching row is returned. If a region and product pair appears twice, SQL and pandas joins return both rows and multiply the order rows.

Common questions

Can INDEX MATCH match on two columns?
Yes. Multiply one test per column inside MATCH, as in MATCH(1,(A=F2)*(B=G2),0). Add another factor for each extra column you need to match.
Why does it return #N/A?
No row matched both values. Check for trailing spaces and for numbers stored as text in either column, since the comparison treats 101 and "101" as different.
Is there a version without an array formula?
Add a helper column that joins the two keys, for example =A2&"|"&B2, and run a normal INDEX MATCH or VLOOKUP on the helper column using F2&"|"&G2 as the lookup value.

Got a version of this with your own ranges?

Paste it into the free translator and get the same breakdown for your exact formula. To run it against a real file, upload the CSV and describe the formula you need in a sentence.