HLOOKUP approximate match with two rate rows
Weight bands across the top, one row per service — pick the band and the row.
Task:
The courier's rate card has weight bands (in kg, from each value up) across row 1, standard rates in row 2 and express rates in row 3. In D6:D9 give each parcel's price for the service shown.
Interactive Spreadsheet
Hints
+5 XPno-hints bonusSolve it on your own to keep the bonus. Each hint gets one step closer to the formula.
Variations of HLOOKUP
0/3The same formula in the other shapes it takes at work.
- Basic HLOOKUP Function
Use HLOOKUP to find values in a horizontally arranged table.
CoreIntermediate - HLOOKUP from a table laid out sideways
When the keys run along the top, look across instead of down.
Beginner - HLOOKUP approximate match with two rate rows
Weight bands across the top, one row per service — pick the band and the row.
Advanced
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 | From kg | 0 | 2 | 5 | 10 | 20 |
| 2 | Standard | 3.5 | 5.2 | 7.9 | 11.4 | 16 |
| 3 | Express | 6.8 | 9.1 | 12.5 | 17.2 | 24 |
| 4 | ||||||
| 5 | Parcel | Weight | Service | Price | ||
| 6 | P-1 | 1.4 | Standard | |||
| 7 | P-2 | 7.2 | Express | |||
| 8 | P-3 | 20 | Standard | |||
| 9 | P-4 | 4.99 | Express |
What this exercise teachesMay contain the answer
Two decisions, two mechanisms: the approximate match picks the column (which band) and the IF picks the row (which service). A 4.99 kg parcel stays in the 2 kg band; a 20 kg parcel is exactly on a boundary and moves into the 20 kg band.