VLOOKUP approximate match for bands
Find which band a number falls into, with the table sorted by each band's starting point.
Task:
Commission depends on the band a rep's sales fall into: the table in E2:F5 lists where each band starts. In C2:C7 give each rep's commission rate.
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 VLOOKUP
0/4The same formula in the other shapes it takes at work.
- Basic VLOOKUP Function
Pull a price out of a second table without going to find it yourself.
CoreIntermediate - VLOOKUP approximate match for bands
Find which band a number falls into, with the table sorted by each band's starting point.
Intermediate - VLOOKUP inside a calculation
Look the price up and multiply by the quantity in the same formula.
Intermediate - VLOOKUP with MATCH choosing the column
Let the column header decide which column comes back.
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 | E | F | |
|---|---|---|---|---|---|---|
| 1 | Rep | Sales | Rate | Band from | Rate | |
| 2 | Aisha | 4200 | 0 | 0.02 | ||
| 3 | Bruno | 12500 | 5000 | 0.04 | ||
| 4 | Chen | 5000 | 10000 | 0.06 | ||
| 5 | Dana | 27800 | 20000 | 0.08 | ||
| 6 | Emil | 19999 | ||||
| 7 | Fatima | 0 |
What this exercise teachesMay contain the answer
An exact match would fail on almost every figure — nobody sells exactly 10,000. Approximate match turns the table into bands: 12,500 is not in it, so VLOOKUP settles on 10,000, the last start point it passes. Write each band's lower limit, not its upper one, and keep the column sorted.