📎 This article includes 1 downloadable practice file ↓
Most tutorials tell you to always end VLOOKUP with FALSE. That is right for IDs and names — but for bands (tax slabs, discount tiers, grades) approximate match is exactly what you want.
The idea
With approximate match, VLOOKUP finds the largest value that is less than or equal to what you look up. Build a table of the starting point of each band:
| From (₹) | Commission |
|---|---|
| 0 | 0% |
| 50,000 | 2% |
| 1,00,000 | 3.5% |
| 2,50,000 | 5% |
=VLOOKUP(B2, $E$2:$F$5, 2, TRUE)Sales of ₹1,40,000 fall between 1,00,000 and 2,50,000, so the formula returns 3.5%.
The one rule
Grades example
=VLOOKUP(C2, {0,"F";40,"D";55,"C";70,"B";85,"A"}, 2, TRUE)The table can even live inside the formula as an array constant, handy for small fixed lists.
Slab tax (each band taxed at its own rate)
Income tax is not one rate on the whole income — each slice is taxed at its rate. Keep a cumulative-tax column in the table (tax payable at the start of each slab), then:
=VLOOKUP(B2,Slabs,3,TRUE) + (B2 - VLOOKUP(B2,Slabs,1,TRUE)) * VLOOKUP(B2,Slabs,2,TRUE)Column 1 = slab start, column 2 = rate, column 3 = tax on all lower slabs. This pattern works for any tiered pricing.
📎 Practice files for this article
- 📗Lookup scenarios workbookVLOOKUP, HLOOKUP, INDEX/MATCH and XLOOKUP side by side — vertical, horizontal, two-way — plus 6 real failure cases and their fixes, with PASS/FAIL checks.⬇ XLSX · 10 KB
Free to use for learning. Files with macros (.bas) are plain text — import them with Alt+F11 → File → Import File, and always test on a copy.
Stuck on a step? Ask a question and the AI answers using this article.