Exercises / Lookups · 2 of 10
Commission tiers with an approximate match
With TRUE as the last argument, VLOOKUP finds the largest value that is less than or equal to what you look up. That is how you read a bracket table: tax bands, discount tiers, grade boundaries. The first column must be sorted smallest to largest.
Your taskIn C2, use VLOOKUP to look up the commission rate for the sales in B2 in the tier table E2:F6 (each row is the rate from that amount up). It will be copied down to C8.
Pro
Unlock this exercise and every module after the basics.
Pro opens every exercise in every module, your review schedule, and the final test for the course certificate.
| Row | A | B | C | D | E | F |
|---|---|---|---|---|---|---|
| 1 | Rep | Sales | Rate | From | Rate | |
| 2 | Ana | $4,200.00 | $0.00 | 0% | ||
| 3 | Ben | $12,500.00 | $5,000.00 | 2% | ||
| 4 | Cara | $5,000.00 | $10,000.00 | 4% | ||
| 5 | Dino | $27,800.00 | $20,000.00 | 6% | ||
| 6 | Ema | $9,999.00 | $50,000.00 | 8% | ||
| 7 | Femi | $61,000.00 | ||||
| 8 | Gus | $19,500.00 |