Commission from a tier table (approximate INDEX MATCH)
The problem
Problem statement
Commission is not a flat rate: the tier table in F2:G5 maps a sales floor to a rate, and the rep's total sales are in B2. Put the commission amount (sales × the rate for their tier) in the Commission cell D2.
The tier must be found by formula — a rep landing exactly on a tier floor gets that tier's rate, not the one below.
Problem data
Show the sheet as a table (selectable, copyable)
| A | B | D | F | G | |
|---|---|---|---|---|---|
| 1 | Rep | Sales | Commission | From | Rate |
| 2 | Ben | 12000 | 0 | 0.03 | |
| 3 | Ada | 4200 | 5000 | 0.05 | |
| 4 | Cleo | 31000 | 15000 | 0.08 | |
| 5 | 30000 | 0.11 |
Notes
MATCH's third argument controls the match type: 0 is exact, 1 finds the largest value less than or equal to the lookup, which is what a tier table needs. It requires the table to be sorted ascending, and it returns a position — so INDEX turns that position into the rate. VLOOKUP with TRUE does the same in one step.
How to approach
Your answer goes in D2. On submit we re-run your formula against 3 different sets of input, so a hardcoded number will fail — the formula has to follow the cells.
- Target cell is D2
- The tier table is sorted ascending, so you want the approximate match type
- MATCH gives the row inside F2:F5; INDEX turns it into a rate
- Shape: =B2*INDEX(?, MATCH(?, ?, ?))
Official solutions
Show 2 solutions — try it yourself first
Approach 1: INDEX with an approximate MATCH
=B2*INDEX($G$2:$G$5, MATCH(B2, $F$2:$F$5, 1))
12000 sits above the 5000 floor and below 15000, so MATCH returns row 2 (5%). 12000 × 5% = 600. On the boundary, 15000 returns the 8% tier, not 5%.
Approach 2: VLOOKUP with TRUE
=B2*VLOOKUP(B2, $F$2:$G$5, 2, TRUE)
The fourth argument TRUE means approximate match: VLOOKUP finds the largest floor that is not above the sales figure and returns column 2, the rate. The table must stay sorted or this silently returns the wrong tier.
Function guide
FAQ
How do I look up a tier instead of an exact value?
Use match type 1: MATCH(sales, floors, 1) or VLOOKUP(sales, table, 2, TRUE). Both find the largest floor not above the lookup value, so the table must be sorted ascending.
Why did my approximate lookup return the wrong rate?
Either the table is not sorted ascending, or the lookup value falls below the first floor — in both cases Excel returns something unexpected rather than an error.