Excel track · Exercise 48 of 86 Paid
INDEX and MATCH summary
Read a rate from a sorted band table with INDEX and an approximate MATCH.
Intermediate
Three commission bands
Read-only data · answer in B6
In B6, return the commission rate for the units in B5 from the band table.
| A | B | |
|---|---|---|
| 1 | Units from | Rate |
| 2 | 0 | 0.02 |
| 3 | 100 | 0.04 |
| 4 | 200 | 0.06 |
| 5 | Units sold | 150 |
| 6 | Rate |
Reference answer
What it does
The band table in A2:B4 is read as ranges. 150 sits above the 100 in A3 and below the 200 in A4, so the band that applies starts on row 3 and pays 0.04. MATCH with 1 returns 2, the position of that starting point, and INDEX reads B3.
A third argument of 1 asks for the last starting point at or below the value, which assumes the column is sorted ascending. The column here runs 0, 100, 200. Set 0 instead and MATCH looks for a row that says exactly 150, finds none, and returns #N/A.
The quiet failure is a band column that has drifted out of order. A figure below the first threshold fails loudly, because MATCH finds nothing and the cell shows #N/A. Re-sort the column and check the first row before trusting any rate.
Syntax
=INDEX(rates, MATCH(units, bands, 1))Worked example
On Three commission bands, =INDEX(B2:B4,MATCH(B5,A2:A4,1)) returns 0.04.
=INDEX(B2:B4,MATCH(B5,A2:A4,1))Common errors
| Error | What it means and how to fix it |
|---|---|
#N/A | The units figure is below the first starting point in A2, so no band applies and there is no rate to read. |
Wrong band | The band column is not sorted ascending, so MATCH settles on a neighbouring row and returns its rate. |
At work
Commission and tax bands, where the rate changes at thresholds and the table is written smallest first.