A carrier's rate card is read two ways at once: down to the weight band a parcel falls into, and across to its exact delivery zone. INDEX with two MATCHes does both, as long as each MATCH is told which kind of match it is making.
You pack and ship orders for an online homeware shop. The carrier's rate card (E1:H6) lists a price for each weight band down the side and each delivery zone across the top; a band runs from its "Weight from" figure up to just under the next one, and the 20 kg band covers anything 20 kg or heavier. In D2, look up the postage for each parcel using its weight in column B and its zone in column C, then fill down to row 7.
Solve it on your own to keep the bonus. Each hint gets one step closer to the formula.
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 | G | H | |
|---|---|---|---|---|---|---|---|---|
| 1 | Parcel | Weight (kg) | Zone | Postage | Weight from (kg) | Zone 1 | Zone 2 | Zone 3 |
| 2 | ORD-3301 | 0.8 | Zone 2 | 0 | 4.5 | 5.95 | 7.8 | |
| 3 | ORD-3302 | 1 | Zone 1 | 1 | 6.95 | 8.4 | 11.25 | |
| 4 | ORD-3303 | 4.99 | Zone 3 | 5 | 11.4 | 14.1 | 19.6 | |
| 5 | ORD-3304 | 12.3 | Zone 2 | 10 | 18.75 | 23.3 | 32.45 | |
| 6 | ORD-3305 | 20 | Zone 3 | 20 | 29.9 | 37.15 | 51.8 | |
| 7 | ORD-3306 | 7.5 | Zone 1 |
The two MATCHes are doing different jobs, and the formula only works because each is told which. The weight column holds the start of each band, not every possible weight, so MATCH(B2,$E$2:$E$6,1) walks down the sorted list and stops at the last figure that does not exceed the parcel — 0.8 kg stops at 0, 12.3 kg stops at 10. That is why ORD-3303, at 4.99 kg, still pays the 1 kg rate: it has not reached 5, and a hair under a band's boundary is a whole band cheaper. ORD-3302 and ORD-3305 sit exactly on a boundary, and "less than or equal to" puts them in the band that starts there, which is what the card means by "Weight from". The zone is the opposite case: "Zone 2" is a label, not a point on a scale, so MATCH(C2,$F$1:$H$1,0) insists on an exact hit — an approximate match on text would quietly land on a neighbouring zone rather than report a typo. INDEX then takes the band's row number and the zone's column number and returns the one price where they cross. Match type 1 trusts that the weights run smallest first; if the carrier's card were ever pasted in heaviest-first, it would return wrong prices without an error, so the order of column E is part of the formula's correctness.