IntermediateLookup & Reference
Look up a customer's region with XLOOKUP
Match a customer ID against a reference table to return their region.
XLOOKUP
8 min25 points
Look up a shipping band with VLOOKUP
The situation
Harbor Supply's carrier prices by weight band rather than exact weight — anything from 25 up to 50 pounds ships at one rate. A parcel weighing 32 pounds has to find the band it falls into, and 32 appears nowhere in the rate card.
A1
fx
| Row number | A | B | C | D | E | F | G |
|---|---|---|---|---|---|---|---|
| 1 | Parcel | Weight | Rate | Weight from | Rate | ||
| 2 | PKG-3301 | 32.00 | 0 | $13 | |||
| 3 | PKG-3302 | 8.00 | 10 | $18 | |||
| 4 | PKG-3303 | 64.00 | 25 | $28 | |||
| 5 | PKG-3304 | 120.00 | 50 | $44 | |||
| 6 | PKG-3305 | 27.00 | 100 | $76 | |||
| 7 |
Functions in this exercise
- VLOOKUP — Finds a value in the first column of a table and returns a value from another column.
Who this is for
Recommended next
Related exercises
- IntermediateLookup & Reference
Look up a unit price with VLOOKUP
Use VLOOKUP with exact match to pull prices from a product table.
VLOOKUP
8 min25 points - IntermediateLogical Functions
Apply commission tiers with IFS
Return the right rate from several tiers without nesting.
IFS
10 min25 points - IntermediateLookup & Reference
When can VLOOKUP not do the job?
Identify the one situation VLOOKUP genuinely cannot handle.
VLOOKUP · XLOOKUP
3 min20 points