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.
=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
- 01($A$2:$A$500=F2) tests every region in the column and returns a list of TRUE and FALSE values.
- 02($B$2:$B$500=G2) does the same for the product column.
- 03Multiplying the two lists turns TRUE into 1 and FALSE into 0, so a row scores 1 only when region and product both match.
- 04MATCH(1,...,0) finds the position of the first 1 in that list. The 0 forces an exact match.
- 05INDEX returns the value at that position from the Unit price column.
The same logic in four tools
=INDEX($C$2:$C$500,MATCH(1,($A$2:$A$500=F2)*($B$2:$B$500=G2),0))=ARRAYFORMULA(INDEX($C$2:$C$500,MATCH(1,($A$2:$A$500=F2)*($B$2:$B$500=G2),0)))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;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.