Work out shipping cost from a weight bracket table
The lookup that doesn't need an exact hit — just the bracket a number falls into.
Task:
You run fulfillment for an online furniture-fittings shop. Shipping is priced in weight brackets, and the rate table only lists where each bracket starts — there is no row for every possible weight. Work out the shipping cost for each order in column C.
Learning Objectives:
- Use VLOOKUP's approximate-match mode to place a value in a bracket, not just match it to a fixed key
- Recognize that a bracket lookup table has to be sorted ascending for approximate-match VLOOKUP to work
- Tell exact-match VLOOKUP (FALSE) apart from approximate-match VLOOKUP (TRUE), and know which one a table calls for
Solve without hints for +5 XP
Interactive Spreadsheet
The data in this exercise
This is the grid you start with. Cell references in the task — B6, C2 — point at the row numbers and column letters below.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Package | Weight (kg) | Shipping cost | Weight (kg) and over | Rate | |
| 2 | Order #4471 | 3.2 | 0 | 4.99 | ||
| 3 | Order #4472 | 6.8 | 5 | 8.99 | ||
| 4 | Order #4473 | 12.5 | 10 | 14.99 | ||
| 5 | Order #4474 | 27 | 20 | 24.99 | ||
| 6 | 50 | 39.99 |
What this exercise teaches (contains the answer)
Approximate-match VLOOKUP doesn't hunt for an exact hit — with TRUE, it walks the first column of the table and keeps the last row whose value is still less than or equal to what it's searching for, then stops there. That only works because the bracket table is sorted ascending by its "and over" column: out of order, VLOOKUP would stop at the wrong row and never notice, since it has no way to tell a sorted column from an unsorted one. A 6.8kg package matches the 5kg-and-over row rather than the 10kg one, because 10 is past it; a 12.5kg package clears the 10kg row but not the 20kg one, so it lands on the 14.99 rate. Reaching for FALSE here, out of habit from an exact-match lookup, would return #N/A for every weight that isn't precisely 0, 5, 10, 20 or 50 kg — which in practice is every real package.