The formula
Excel & Google SheetsExcelGoogle Sheets
=VLOOKUP(B7,A2:B5,2,TRUE)In E2 → 5%
Excel 365 / Google SheetsExcelGoogle Sheets
=XLOOKUP(B7,A2:A5,B2:B5,,-1)In E3 → 5%
Commission amountExcelGoogle Sheets
=B7*VLOOKUP(B7,A2:B5,2,TRUE)In E4 → $312.50
✓ Tested. All 3 Excel results in this example were recalculated in LibreOffice Calc 25.2 and matched the values shown.
Worked example
Paste the data below into a blank sheet starting at cell A1 (or download the cheat sheet, where every example is set up). Highlighted cells hold the formulas and show the expected results.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | From | Rate | |||
| 2 | 0 | 2% | 5% | ||
| 3 | 1000 | 3% | 5% | ||
| 4 | 5000 | 5% | $312.50 | ||
| 5 | 10,000 | 7% | |||
| 6 | |||||
| 7 | Sales | 6250 |
How it works
- A2:B5
- Tier table: lower bound in the first column, sorted smallest to largest.
- TRUE
- Approximate match: returns the largest lower bound that is ≤ the lookup value.
- -1 (XLOOKUP)
- match_mode -1 = exact match or the next smaller item. Works even if the table isn't sorted.
Common errors and fixes
Why does the formula return #N/A?
The value is smaller than the first tier. Start the table at 0.
Wrong tier?
The VLOOKUP table isn't sorted ascending. Sort it, or use XLOOKUP with -1.
Does this work in Google Sheets?
Yes. The formulas on this page work the same way in Google Sheets.