The smallest box that fits with XLOOKUP
PremiumIn C2, pick the smallest box that holds the volume in B2. If there is no exact match, XLOOKUP should take the next larger size: match_mode 1, the fifth argument, XLOOKUP(B2,$E$2:$E$5,$F$2:$F$5,,1).
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Order | Volume (liters) | Box | Holds up to | Box | ||
| 2 | 1001 | 12 | 5 | Small | |||
| 3 | 1002 | 4 | 10 | Medium | |||
| 4 | 1003 | 27 | 20 | Large | |||
| 5 | 1004 | 20 | 100 | Extra large | |||
| 6 | |||||||
| 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
match_mode 1 means "exact match, or else the next larger value". There's no 12-liter box, so XLOOKUP takes the next size up, 20 liters, the Large box. Filled down, 4 liters gets Small, 27 Extra large and 20, an exact match, Large too.
Common mistake: Using -1, the next smaller value. For sizes and capacities that picks a box that is too small.
The answer
- C2
=XLOOKUP(1B2,2$E$2:$E$5,3$F$2:$F$5,,51)
In plain words: Find 1the volume in B2 in 2the sizes in E2:E5 and return 3the box from F2:F5, 5taking the next larger size when there is no exact match.