Skip to content

Lookup with two criteria

Return a value where two columns both match, for example a price by product and size.

The formula

Argument separator
Excel · Excel 365 and Excel 2021
=XLOOKUP(1, (A2:A100=F2)*(B2:B100=G2), C2:C100, "Not found")
Google Sheets
=IFERROR(INDEX(FILTER(C2:C100, A2:A100=F2, B2:B100=G2), 1), "Not found")
For Excel 2019 and older
=IFERROR(INDEX(C2:C100, MATCH(1, INDEX((A2:A100=F2)*(B2:B100=G2), 0), 0)), "Not found")

How it works

  1. (A2:A100=F2)*(B2:B100=G2)

    Gives 1 for rows where both conditions are true and 0 everywhere else.

  2. XLOOKUP(1, …

    Finds the first row that scored 1.

  3. C2:C100

    The column to return from.

Example

F: ProductG: SizeH: Result (price)
T-shirtM15
T-shirtXL17
HoodieS35

Tips

  • Add more conditions by multiplying more brackets, for example *(D2:D100=H2).