About this lesson
VLOOKUP approximate match: volume rebate tiers in Google Sheets
In Google Sheets, VLOOKUP with TRUE returns the row for the largest value less than or equal to the one you look up: =VLOOKUP(B9,$A$2:$B$6,2,TRUE). The first column must be sorted smallest to largest. The newer XLOOKUP function does it with match mode -1 and no sorting: =XLOOKUP(B9,$A$2:$A$6,$B$2:$B$6,,-1).
Using Excel? Do this lesson in Excel.
=VLOOKUP(B9,$A$2:$B$6,2,TRUE)What you'll practise
Look up each customer's rebate tier with VLOOKUP's approximate match, see why the tier table must be sorted, then switch to XLOOKUP, which doesn't need the table in order.
4 steps · 40 points · the cells outlined in gold below
- VLOOKUP
- approximate match
- tier tables
- XLOOKUP match mode
The data
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Spend from ($k) | Rebate rate | ||
| 2 | 0 | 0.00% | ||
| 3 | 250 | 1.50% | ||
| 4 | 500 | 2.50% | ||
| 5 | 1000 | 3.50% | ||
| 6 | 2000 | 4.50% | ||
| 7 | ||||
| 8 | Customer | Spend ($k) | Rebate rate | Rebate ($k) |
| 9 | Mallee Grain | 612.4 | ||
| 10 | Otway Foods | 1,457.9 | ||
| 11 | Riverina Wines | 184.6 | ||
| 12 | Pilbara Mining Services | 2,731.2 | ||
| 13 | Kimberley Cement | 250.0 | ||
| 14 | Barossa Packaging | 978.3 |