VLOOKUP Approximate Match: Tax Slabs, Commissions and Grades

VLOOKUP Approximate Match: Tax Slabs, Commissions and Grades featured illustration

📎 This article includes 1 downloadable practice file ↓

⏱ 3 min readUpdated 5 October 2026

Exact match finds a code. Approximate match finds the band a number falls into: the commission rate for a sales figure, the grade for a mark, the discount for an order value. It replaces long nested IFs with a small, editable table.

In this article
  1. How it works
  2. Grades
  3. Progressive tax slabs
  4. Where people go wrong
  5. Continue learning

How it works

Put each band’s lower limit in the first column, sorted smallest to largest. Approximate match returns the row with the largest limit that is not more than your value.

Advertisement
From Commission
0 0%
1,00,000 2%
5,00,000 3.5%
10,00,000 5% 
=VLOOKUP(B2, $H$2:$I$5, 2, TRUE)            ' 6,40,000 → 3.5%
=XLOOKUP(B2, $H$2:$H$5, $I$2:$I$5, , -1)     ' same; -1 = exact or next smaller
=INDEX($I$2:$I$5, MATCH(B2, $H$2:$H$5, 1))   ' same in any Excel

Grades

Table: 0 F, 40 C, 60 B, 75 A, 90 A+. =XLOOKUP(B2, Bands[From], Bands[Grade], , -1). Change a boundary by editing the table, not twenty formulas.

Progressive tax slabs

A flat lookup gives the rate of the top slab, but slab taxes apply each rate only to the income within that slab. With slabs in a table (From, Rate), use the “differential rate” method:

' H2:H6 = slab lower limits, J2:J6 = rate increase over the previous slab (first row = first rate)
=SUMPRODUCT((B2 > H2:H6) * (B2 - H2:H6) * J2:J6)

Each term adds the extra rate on income above that slab’s start. Rates and slabs change in Budgets; keep them in a table labelled with the year, and treat any figures you use as examples to verify against official sources.

⚠️ Approximate match on an unsorted table returns wrong answers without any error. Sort the From column ascending and keep it sorted, or use XLOOKUP’s -1 mode, which doesn’t need sorting.
💡 Always test the boundaries: exactly 1,00,000, one rupee below, and zero. That’s where slab formulas go wrong.

Where people go wrong

Mistake Effect
Upper limits in the first column Every value lands one band too high
Missing the 0 row Small values return #N/A
TRUE used for codes Wrong matches; codes need FALSE / exact
⚡ Interactive Practice Lab & Formula Tester:

Test formulas dynamically in our web formula laboratory or download the companion Excel workbook.

Continue learning

All Excel Formulas guides →
Dependent Dropdown Lists in Excel (Two Methods That Actually Work)Create a second dropdown that changes with the first — Category then Product, Country…Why 0.1 + 0.2 Is Not Exactly 0.3 (and Why Excel Sometimes Agrees)Computers store decimals in binary, so 0.1 + 0.2 becomes 0.30000000000000004. Why it happens,…Excel’s Strangest Behaviours Explained: 15 “Bugs” That Are Actually FeaturesWhy Excel turns 1-2 into a date, drops leading zeros, shows ####, changes long…Why Does Excel Have Exactly 1,048,576 Rows? The Logic Behind Software LimitsWhy Excel stops at 1,048,576 rows and column XFD, why old Excel had 65,536…

📎 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.

✨ Ask AI about this article

Stuck on a step? Ask a question and the AI answers using this article.

Free · AI can be wrong