How to / Lookups
How to look up a value with two criteria in Excel
Use =XLOOKUP(1, (A2:A5="Shirt")*(B2:B5="L"), C2:C5). It finds the first row where both conditions are true.
The price of a large shirt
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Product | Size | Price | ||
| 2 | Shirt | M | 20 | 22 | |
| 3 | Shirt | L | 22 | ||
| 4 | Hat | M | 12 | ||
| 5 | Hat | L | 14 |
In E2: =XLOOKUP(1, (A2:A5="Shirt")*(B2:B5="L"), C2:C5) → 22
How it works
- Each test gives TRUE or FALSE for every row. Multiplied together they give 1 where both are true and 0 everywhere else.
- XLOOKUP searches that column of 1s and 0s for a 1.
- It returns the price from the same row.
Variations
In older versions of Excel
A B C D E 1 Product Size Price 2 Shirt M 20 12 3 Shirt L 22 4 Hat M 12 5 Hat L 14 In E2:
=INDEX(C2:C5, MATCH(1, (A2:A5="Hat")*(B2:B5="M"), 0))→ 12In Excel 2019 and earlier, finish it with Ctrl+Shift+Enter.
When the result is a number
A B C D E 1 Product Size Price 2 Shirt M 20 22 3 Shirt L 22 4 Hat M 12 5 Hat L 14 In E2:
=SUMIFS(C2:C5, A2:A5, "Shirt", B2:B5, "L")→ 22If only one row can match, adding up the matches gives that row's value. It does not work for text.
Your own text when nothing matches
A B C D E 1 Product Size Price 2 Shirt M 20 Not found 3 Shirt L 22 4 Hat M 12 5 Hat L 14 In E2:
=XLOOKUP(1, (A2:A5="Coat")*(B2:B5="L"), C2:C5, "Not found")→ Not found
Watch out for
- Joining the columns with &. Looking up
"Shirt"&"L"inA2:A5&B2:B5also works, but "AB"&"C" and "A"&"BC" join to the same text. Multiplying the tests cannot be fooled that way. - More than one match. XLOOKUP returns the first matching row. To see them all, use
=FILTER(C2:C5, (A2:A5="Shirt")*(B2:B5="L")).
Doesn't work on your data? Paste your formula into the formula checker with a few rows of your sheet. It points at the part that fails and says why.
Functions used
- XLOOKUP: Searches a range and returns the matching item from another range. The modern replacement for VLOOKUP.
- INDEX: The value at a given row and column of a range.
- MATCH: The position of a value in a row or column.
- SUMIFS: Adds the cells that meet every one of several conditions.
- FILTER: Keeps only the rows that meet a condition. The result spills.