VLOOKUP with an approximate match
PremiumIn E2, find the commission rate for the sales in D2. The table lists where each band starts, so use VLOOKUP with TRUE for an approximate match.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Sales from | Rate | Sales | Rate | ||
| 2 | 0 | 0.02 | 18000 | |||
| 3 | 10000 | 0.03 | ||||
| 4 | 25000 | 0.05 | ||||
| 5 | 50000 | 0.07 | ||||
| 6 | ||||||
| 7 | ||||||
| 8 |
Premium lesson
Unlock VLOOKUP and every other lesson
This is a premium lesson. Premium lets you solve it and every other lesson, take the timed mock tests, and earn certificates you can share on LinkedIn.
It comes up in this mock test: Excel test for job interviews
Why this works
With TRUE, VLOOKUP finds the last band that starts at or below the sales figure. 18000 is past 10000 but not 25000, so the rate is 3%. The first column must be sorted from smallest to largest.
Common mistake: Using FALSE here. 18000 isn't in the table, so an exact match gives #N/A.
The answer
- E2
=VLOOKUP(1D2,2A2:B5,32,4TRUE)
In plain words: Find 1the sales in D2 in the first column of 2A2:B5 and return 3the rate in the second column, 4taking the last band that is not above it.