# How to look up a value with two criteria in Excel

Multiply two tests to build the lookup array: =XLOOKUP(1,(range1=crit1)*(range2=crit2),result_range).

**Excel:** `=XLOOKUP(1,(A2:A8=G1)*(B2:B8=G2),D2:D8)` (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 | G |
| --- | --- | --- | --- | --- | --- | --- |
| Region | Quarter | Rep | Sales |  | Region | East |
| East | Q1 | Ana | 48200 |  | Quarter | Q2 |
| West | Q1 | Ben | 51900 |  | Sales |  |
| East | Q2 | Ana | 55300 |  |  |  |
| West | Q2 | Ben | 49800 |  |  |  |
| North | Q1 | Cara | 38700 |  |  |  |
| North | Q2 | Cara | 42100 |  |  |  |
| East | Q3 | Dev | 60400 |  |  |  |

Formula in G3: `=XLOOKUP(1,(A2:A8=G1)*(B2:B8=G2),D2:D8)`

Result: $55,300

## How it works

Each comparison returns TRUE or FALSE for every row. Multiplying them turns TRUE×TRUE into 1 and anything else into 0, so only the row that matches both criteria is a 1. XLOOKUP then finds the 1 and returns the sales from that row.

## Other ways

- `=INDEX(D2:D8,MATCH(1,(A2:A8=G1)*(B2:B8=G2),0))`: The same idea with INDEX and MATCH, for Excel 2019 and earlier (confirm with Ctrl+Shift+Enter in Excel 2016 and older).
- `=SUMIFS(D2:D8,A2:A8,G1,B2:B8,G2)`: When the result is a number and each pair appears once, SUMIFS is simplest. It returns 0 instead of #N/A when there is no match.

## Common mistakes

- If two rows match both criteria, XLOOKUP returns the first. Use FILTER to see them all.
- A helper column joining the keys (`=A2&"|"&B2`) is another option, but it has to be kept in sync with the data.

## Questions

**Can VLOOKUP use two criteria?**

Only with a helper column that joins the two keys. XLOOKUP or INDEX/MATCH with multiplied tests is cleaner.

**How do I use three criteria?**

Multiply a third test: `(A2:A8=G1)*(B2:B8=G2)*(C2:C8=G4)`.

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

Source: https://spreadsheetpractice.com/formulas/lookup-with-two-criteria