Concept
1. The idea
In Module 3 we used IFS for grades. When slabs are many or change often, put them in a table and look them up. Approximate match answers: "Which bracket does this number fall into?"
2. Build the bracket table
Write only the lower limit of each bracket, sorted smallest to largest.
| A | B | |
|---|---|---|
| 1 | Min Marks | Grade |
| 2 | 0 | F |
| 3 | 50 | C |
| 4 | 75 | B |
| 5 | 90 | A |
Read it as: 0 and above = F, 50 and above = C, and so on.
3. Three ways to look it up (marks in D2 = 82)
VLOOKUP with TRUE:
=VLOOKUP(D2, $A$2:$B$5, 2, TRUE) → B
XLOOKUP with match_mode -1 (exact or next smaller):
=XLOOKUP(D2, $A$2:$A$5, $B$2:$B$5, , -1) → B
INDEX + MATCH with 1:
=INDEX($B$2:$B$5, MATCH(D2, $A$2:$A$5, 1)) → B
All three find the largest value that is less than or equal to 82, which is 75 → B.
4. The sorting rule
VLOOKUP TRUE and MATCH 1 require the first column sorted in ascending order. If it isn't, they return wrong answers without any error. XLOOKUP with -1 works even on unsorted tables — one more reason to prefer it.
Also start the table at the lowest possible value (0 here). A value below the first row returns #N/A.
5. Commission tiers
| G | H | |
|---|---|---|
| 1 | Min Sales | Rate |
| 2 | 0 | 0% |
| 3 | 25000 | 2% |
| 4 | 50000 | 5% |
| 5 | 100000 | 8% |
Commission for sales in C2:
=C2 * XLOOKUP(C2, $G$2:$G$5, $H$2:$H$5, , -1)
Sales of 62000 → rate 5% → commission 3100. Compare this with the IFS formula in Module 3, Lesson 4: same result, but now changing a rate means editing one cell, not the formula.
6. Tax slabs (progressive)
Slab tax is different: each slab's rate applies only to the income within that slab. The trick is to add a "base tax" column — the total tax up to the start of each slab.
Illustrative slabs for practice only — not current tax law, no cess/rebate.
| J | K | L | |
|---|---|---|---|
| 1 | Lower Limit | Base Tax | Rate |
| 2 | 0 | 0 | 0% |
| 3 | 400000 | 0 | 5% |
| 4 | 800000 | 20000 | 10% |
| 5 | 1200000 | 60000 | 15% |
| 6 | 1600000 | 120000 | 20% |
Base tax is built from the previous row: K4 = K3 + (J4-J3)*L3. Copy it down.
Tax for income in N2:
=XLOOKUP(N2,$J$2:$J$6,$K$2:$K$6,,-1) + (N2 - XLOOKUP(N2,$J$2:$J$6,$J$2:$J$6,,-1)) * XLOOKUP(N2,$J$2:$J$6,$L$2:$L$6,,-1)
Meaning: base tax of the slab + (income − slab lower limit) × slab rate.
Income 10,00,000 → slab 8,00,000 → 20000 + 200000 × 10% = 40,000.
7. When to use approximate match
Grades, discount tiers, commission and incentive slabs, shipping charges by weight, age groups, interest rates by amount. Anywhere a number needs to be placed into a range.
Common mistakes
Writing upper limits instead of lower limits in the table. Unsorted table with VLOOKUP TRUE or MATCH 1. Not starting the table at 0, giving #N/A for small values. Using approximate match for codes or names (use exact match there).