Two-way lookup: an approximate row and an exact column
Weight band down the side, zone across the top — two different kinds of match.
Task:
Postage depends on the weight band (rows, from each weight up) and the zone (columns). In D10:D12 return the price for each parcel with INDEX and two MATCHes.
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 INDEX MATCH
0/3The same formula in the other shapes it takes at work.
- Two-Way Lookup
Perform a two-way lookup to find values at the intersection of a row and column.
CoreAdvanced - Two-way lookup with VLOOKUP and MATCH
VLOOKUP finds the row; MATCH tells it which column.
Advanced - Two-way lookup: an approximate row and an exact column
Weight band down the side, zone across the top — two different kinds of match.
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 | |
|---|---|---|---|---|
| 1 | From kg | Zone 1 | Zone 2 | Zone 3 |
| 2 | 0 | 4.1 | 5.6 | 8.2 |
| 3 | 1 | 5.3 | 7.4 | 10.9 |
| 4 | 3 | 7.8 | 10.2 | 14.6 |
| 5 | 10 | 12.5 | 16.8 | 23 |
| 6 | ||||
| 7 | ||||
| 8 | ||||
| 9 | Parcel | Weight | Zone | Price |
| 10 | X1 | 2.5 | Zone 3 | |
| 11 | X2 | 0.4 | Zone 1 | |
| 12 | X3 | 10 | Zone 2 |
What this exercise teachesMay contain the answer
The two MATCHes are independent, so each can use the match type its axis needs: approximate for a numeric scale, exact for names. A 10 kg parcel sits exactly on a band boundary and takes the 10 kg row.