# How to use XLOOKUP with multiple criteria in Excel

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.

**Excel:** `=XLOOKUP(1,(A2:A9=F1)*(B2:B9=F2),C2:C9)` (Excel 2021, 2024 and Microsoft 365)

**Google Sheets:** the same formula works.

## Example

Sample data (row 1 is the header):

| A | B | C | D | E | F |
| --- | --- | --- | --- | --- | --- |
| Region | Product | Price |  | Region | West |
| East | Laptop | 1200 |  | Product | Dock |
| East | Dock | 180 |  |  |  |
| West | Laptop | 1260 |  |  |  |
| West | Dock | 195 |  |  |  |
| North | Laptop | 1240 |  |  |  |
| North | Dock | 170 |  |  |  |
| South | Laptop | 1180 |  |  |  |
| South | Dock | 165 |  |  |  |
|  |  |  |  | Price |  |

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

Result: 195

## 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.

## Other ways

- `=INDEX(C2:C9,MATCH(1,INDEX((A2:A9=F1)*(B2:B9=F2),0),0))`: Works in versions without XLOOKUP.
- `=XLOOKUP(F1&"|"&F2,A2:A9&"|"&B2:B9,C2:C9)`: 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.

## 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.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-11-20.

Source: https://spreadsheetpractice.com/formulas/xlookup-with-multiple-criteria
