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.
It comes up in this mock test: Excel assessment (Indeed format)
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.