How to do a lookup with two criteria in Excel
Sometimes one column isn’t enough to find the right row, such as a price list with a row for each product in each size.
Multiplying two conditions gives 1 only on the row where both are true, and XLOOKUP can search for that 1.
The formula
| A | B | C | |
|---|---|---|---|
| 1 | Product | Size | Price |
| 2 | Tee | S | 12 |
| 3 | Tee | M | 14 |
| 4 | Cap | M | 9 |
=XLOOKUP(1,(A2:A4="Tee")*(B2:B4="M"),C2:C4)Result: 14
Only the second row is both a Tee and size M.
Step by step
- Type =XLOOKUP(1, to search for the row where everything matches.
- Write each condition in brackets, multiplied together.
- Select the column to return, close the bracket and press Enter.
Other ways to write it
=SUMIFS(C2:C4,A2:A4,"Tee",B2:B4,"M")Works for numbers, as long as each combination appears only once.=XLOOKUP("Tee"&"M",A2:A4&B2:B4,C2:C4)Joins the two columns into one key and searches that.
Each of these gives the same result on the example above, in Excel and in Cellwise.
Common mistakes
- Matching only the first column
- A plain lookup on the product finds the first Tee, whatever its size.
- Using SUMIFS for text
- SUMIFS only works when the answer is a number. Use XLOOKUP for names or codes.
Reading isn’t the same as doing
Two-way lookups gives you real tasks on this, and tests your formulas on data you haven’t seen. It’s free, with no sign-up.