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
A2:A98 rows × 1 col: "East", "East", "West", "West", …A range: 8 cells in one column.
F1"West"The value in F1.
A2:A9=F1{8 rows × 1 col} FALSE, FALSE, TRUE, TRUE, …The = operator tests whether the two sides are equal.
B2:B98 rows × 1 col: "Laptop", "Dock", "Laptop", "Dock", …A range: 8 cells in one column.
F2"Dock"The value in F2.
B2:B9=F2{8 rows × 1 col} FALSE, TRUE, FALSE, TRUE, …The = operator tests whether the two sides are equal.
A2:A9=F1)*(B2:B9=F2{8 rows × 1 col} 0, 0, 0, 1, …The * operator multiplies.
C2:C98 rows × 1 col: $1,200, $180, $1,260, $195, …A range: 8 cells in one column.
XLOOKUP(1,(A2:A9=F1)*(B2:B9=F2),C2:C9)195XLOOKUP: Searches one range and returns the matching item from another.- lookup_value
1 - lookup_array
A2:A9=F1)*(B2:B9=F2{8 rows × 1 col} 0, 0, 0, 1, … - return_array
C2:C98 rows × 1 col: $1,200, $180, $1,260, $195, …
- lookup_value
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.
| Formula | Result | When to use it |
|---|---|---|
| =INDEX(C2:C9,MATCH(1,INDEX((A2:A9=F1)*(B2:B9=F2),0),0)) | 195 | Works in versions without XLOOKUP. |
| =XLOOKUP(F1&"|"&F2,A2:A9&"|"&B2:B9,C2:C9) | 195 | Joins 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
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-21: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.