Excel Lookup With Two Criteria Formula
By Szabó Gergő · Updated
Match two columns at once, such as region and product, then return a price or status from the same row.
Syntax and arguments
=XLOOKUP(1,(A2:A100=G2)*(B2:B100=H2),C2:C100,"Not found")- criteria 1
- First test, such as region equals G2.
- criteria 2
- Second test, such as product equals H2.
- return range
- The column to return from the matching row.
Two-criteria lookup examples
Region A, product B, price C.
=XLOOKUP(1,(A2:A200=G2)*(B2:B200=H2),C2:C200,"None")TRUE*TRUE is 1, so XLOOKUP finds the first row that passes both tests.
Microsoft 365.
=IFERROR(INDEX(FILTER(C2:C200,(A2:A200=G2)*(B2:B200=H2)),1),"None")FILTER can return many rows; INDEX takes the first.
Excel without XLOOKUP.
=INDEX(C2:C200,MATCH(1,INDEX((A2:A200=G2)*(B2:B200=H2),0),0))The INDEX wrapper lets MATCH run as an array in older Excel.
Common mistakes
Using & to join keys without unique pairs
If two rows share the same region-product pair, decide whether you want first, last, or a spill.
Text vs number IDs
Trim both sides or convert with VALUE/TEXT before matching.
Returning #N/A with no fallback
Pass if_not_found to XLOOKUP or wrap FILTER in IFERROR.
Two-criteria lookup FAQ
Is SUMIFS a lookup?
SUMIFS adds numbers. Use it only when the return column is numeric and you want a total, not a label.
Can I add a third criterion?
Yes. Multiply another TRUE/FALSE array: *(D2:D200=I2).
Does this work left of the key?
Yes with XLOOKUP or INDEX MATCH. VLOOKUP cannot look left.