Spreadsheet Practice

How to use XLOOKUP with multiple criteria in Excel

Formula (Excel and Google Sheets)

=XLOOKUP(1,(A2:A9=F1)*(B2:B9=F2),C2:C9)

Multiply the conditions into one array and look up 1: =XLOOKUP(1,(A2:A9=G2)*(B2:B9=G3),C2:C9). A row matches only when every condition is true.

Result on the example
195 in G3
Works in
Excel 2021, 2024 and Microsoft 365
Google Sheets
Same formula

See it work

The formula is already in G3. Change any input and the result updates, or edit the formula to experiment.

How it works

Each comparison returns an array of TRUE and FALSE. Multiplying two of them gives 1 only where both are true and 0 elsewhere, so there is exactly one 1 for a unique region and product. XLOOKUP finds that 1 and returns the price from the same row.

=XLOOKUP(1,(A2:A9=F1)*(B2:B9=F2),C2:C9)

Result195

  1. A2:A98 rows × 1 col: "East", "East", "West", "West", …

    A range: 8 cells in one column.

  2. F1"West"

    The value in F1.

  3. A2:A9=F1{8 rows × 1 col} FALSE, FALSE, TRUE, TRUE, …

    The = operator tests whether the two sides are equal.

  4. B2:B98 rows × 1 col: "Laptop", "Dock", "Laptop", "Dock", …

    A range: 8 cells in one column.

  5. F2"Dock"

    The value in F2.

  6. B2:B9=F2{8 rows × 1 col} FALSE, TRUE, FALSE, TRUE, …

    The = operator tests whether the two sides are equal.

  7. A2:A9=F1)*(B2:B9=F2{8 rows × 1 col} 0, 0, 0, 1, …

    The * operator multiplies.

  8. C2:C98 rows × 1 col: $1,200, $180, $1,260, $195, …

    A range: 8 cells in one column.

  9. XLOOKUP(1,(A2:A9=F1)*(B2:B9=F2),C2:C9)195

    XLOOKUP: Searches one range and returns the matching item from another.

    • lookup_value1
    • lookup_arrayA2:A9=F1)*(B2:B9=F2{8 rows × 1 col} 0, 0, 0, 1, …
    • return_arrayC2:C98 rows × 1 col: $1,200, $180, $1,260, $195, …

In Google Sheets

The same formula works in Google Sheets: =XLOOKUP(1,(A2:A9=F1)*(B2:B9=F2),C2:C9). We ran it in Google Sheets mode on the same data and it returns 195.

Other ways to do it

Each one was run on the same example sheet.

FormulaResultWhen to use it
=INDEX(C2:C9,MATCH(1,INDEX((A2:A9=F1)*(B2:B9=F2),0),0))195Works in versions without XLOOKUP.
=XLOOKUP(F1&"|"&F2,A2:A9&"|"&B2:B9,C2:C9)195Joins the criteria into one key.

Common mistakes

  • If two rows meet every condition, only the first is returned.
  • Wrap in IFNA(…,"Not found") for a friendly message when nothing matches.

Try it yourself

Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.

Use XLOOKUP with multiple criteria

In G3, look up the price for the region in F1 and the product in F2.

Questions

Can I use OR instead of AND?

Add the arrays instead of multiplying: (A2:A9=F1)+(B2:B9=F2), then look up a value greater than 0.

Checked by running on 2026-11-20: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.