Topic 06

VLOOKUP Approximate

In Topic 04 we always used 0 as the 4th argument to force exact match — and called approximate match a problem. This topic covers the one real-world scenario where approximate match is the right tool: bracket lookups, where a value falls within a range and you need the corresponding tier.

Classic lookup group Tax bracket use case Training file: vlookup-approx.xlsx

The Problem — Nested IF Gets Out of Hand

The file has 741 employees with salaries. Each salary falls into a tax bracket and needs a tax rate assigned. The first instinct is to use IF — but with 11 brackets, that means 10 nested IFs stacked inside each other.

❌ Nested IF approach

Unreadable, fragile, hard to maintain

=IF(F2<5000, 0%,
IF(F2<15000, 1%,
  IF(F2<25000, 3%,
   IF(F2<35000, 5%,
    IF(F2<45000, 6%,
     IF(F2<55000, 7%,
      IF(F2<65000, 8%,
       IF(F2<75000, 10%,
        IF(F2<85000, 11%,
         IF(F2<95000, 12%,
          13%))))))))))

10 nested IFs. Add one bracket and the whole formula changes.

✓ VLOOKUP Approximate approach

One formula. The table does all the work.

=VLOOKUP(F2, $J$1:$K$12, 2, 1)

11 brackets, 1 formula. Add a new bracket by adding a row to the table — formula unchanged.

The Tax Bracket Table

The table defines the lower boundary of each bracket. A salary is in bracket X if it is greater than or equal to the amount in that row and less than the amount in the next row.

amount	percentage
0	0%
5,000	1%
15,000	3%
25,000	5%
35,000	6%
45,000	7%
55,000	8%
65,000	10%
75,000	11%
85,000	12%
95,000	13%
Each row = lower boundary of a bracket

📌

How to read the table

A salary of 41,639 is between 35,000 and 45,000, so the tax rate is 6%.

A salary of 56,469 is between 55,000 and 65,000, so the tax rate is 8%.

The amount column shows where each bracket starts, not where it ends.

How Approximate Match Works

When the 4th argument is 1 (or TRUE), Excel scans the first column from top to bottom and finds the largest value that is less than or equal to the lookup value. The moment the next value would exceed it, Excel stops and returns the result from the current row.

Example trace — looking up salary 41,639

Amount (col 1)	Percentage (col 2)	Decision
0	0%	0 ≤ 41,639 — keep going
5,000	1%	5,000 ≤ 41,639 — keep going
15,000	3%	15,000 ≤ 41,639 — keep going
25,000	5%	25,000 ≤ 41,639 — keep going
35,000	6%	35,000 ≤ 41,639 ✓ — candidate
45,000	7%	45,000 > 41,639 — stop. Return previous row → 6%

💡

The rule in one sentence

Approximate match returns the result from the row with the largest value that does not exceed the lookup value. It's the "how much have you earned up to this point" logic — used for tax brackets, commission tiers, grade bands, discount levels, and any stepped scale.

⬆️

Critical Requirement

The lookup column MUST be sorted ascending

Approximate match depends on the lookup column being in ascending order (smallest to largest). Excel's scan logic assumes the values are sorted — if they are not, it may stop at the wrong row and return a completely wrong result with no error.

This is different from exact match (0), where order does not matter. With approximate match (1), sorting is not optional. Always verify your bracket table is sorted before relying on this formula.

Syntax — Same VLOOKUP, Different 4th Argument

= VLOOKUP(lookup_value, table_array, col_index_num, 1)

lookup_value The salary value to classify — F2. Use a relative reference this time so it adjusts as you copy down the rows.

table_array The tax bracket table — $J$1:$K$12. Lock it with $ so it stays fixed as the formula is copied down all 741 rows.

col_index_num 2 — the percentage is in the second column of the bracket table.

              range_lookup
              1 — this is the key difference. Unlike Topic 04 where we always wrote 0, here we intentionally write 1 to enable approximate match. This is the one scenario where it's the right choice.
            

Training Exercise

Open vlookup-approx.xlsx. The staff data is in columns A–G. The tax bracket table is in columns J and K. The TaxRate column (H) is empty — fill it for all 741 employees using one VLOOKUP formula copied down.

Download vlookup-approx.xlsx

Cell	Formula	Notes
H2	`=VLOOKUP(F2, $J$1:$K$12, 2, 1)`	Then copy down to H742

✅

Verify your results

Check a few rows manually against the bracket table: Salary 41,639 → bracket starts at 35,000 → 6%. Salary 56,469 → bracket starts at 55,000 → 8%. Salary 28,122 → bracket starts at 25,000 → 5%.

⚠️

Updating the tax table in the future

If the tax brackets change, you only update the table in columns J–K. The formula in column H does not need to change at all. This is the maintainability advantage over the nested IF approach — the data and the logic are separated.

Topic 05 — XLOOKUP

Topic 07 — FILTER