XLOOKUP with two criteria
PremiumIn F3, return the sales for the region in F1 and the product in F2. Look up 1 in the product of the two tests: XLOOKUP(1,(A2:A6=F1)*(B2:B6=F2),C2:C6).
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Region | Product | Sales | Region | North | ||
| 2 | North | Shoes | 420 | Product | Bags | ||
| 3 | South | Bags | 310 | Sales | |||
| 4 | North | Bags | 150 | ||||
| 5 | East | Shoes | 280 | ||||
| 6 | South | Shoes | 500 | ||||
| 7 | |||||||
| 8 |
Premium lesson
Unlock XLOOKUP and every other lesson
This is a premium lesson. Premium lets you solve it and every other lesson, take the timed mock tests, and earn certificates you can share on LinkedIn.
Your first month for $9.50 $19
50% off your first month when you join today.
Then $19/month. Cancel any time, and get a full refund within 14 days if it isn't for you.
Why this works
(A2:A6=F1) gives TRUE or FALSE for each row, and so does (B2:B6=F2). Multiplied, TRUE counts as 1 and FALSE as 0, so only row 4, North and Bags, gives 1. XLOOKUP finds that 1 and returns the sales in the same row, 150.
Common mistake: Adding the two tests with + instead of multiplying. That gives 1 for rows where only one of them is true, such as North and Shoes.
The answer
- F3
=XLOOKUP(11,2(A2:A6=F1)*(B2:B6=F2),3C2:C6)
In plain words: Find the row where 2the region is F1 and the product is F2, marked by 1a 1, and return 3its sales from C2:C6.