← All Kits · Excel Kit

Approximate Match Lookups: Grades, Tax Bands and Anything With Cut-Offs

Michael Nocito · Updated August 2026 · Every number on this page was worked before it was published

Scores turn into grades. Amounts turn into tax bands. Order sizes turn into discount tiers. Every one of these is a rule with cut-offs, and almost every one gets written first as a stack of nested IFs that nobody can review.

What you do: put the cut-offs in a two-column table, sorted ascending, and look the value up with an approximate match. The rule becomes something a colleague can read, and changing it means editing a cell.

The short version. Lower bounds, ascending, no gaps. Then one lookup does the whole rule.

The band table

Five rows. Each says: from this number upward, until the next row, the answer is this. The first row starts at 0 so that every possible score has a band, which is the detail that stops the formula erroring on a low value.

=INDEX($F$2:$F$6, MATCH(B2, $E$2:$E$6, 1))

Or the older form, which needs the label to be to the right of the bound:

=VLOOKUP(B2, $E$2:$F$6, 2, TRUE)

The 1 in MATCH and the TRUE in VLOOKUP are the same instruction: find the largest value that is less than or equal to this, and give me its position.

Worked, including the boundary

A score of exactly 70 gets a B. That is a decision, not an accident: with lower bounds, a value equal to a bound belongs to that band. If your policy says 70 is a C, the bound must be 70.01, or better, 71 if scores are whole numbers. Write the policy in a cell next to the table so nobody has to reverse-engineer it from the numbers.

The four rules

1. Sort the bounds ascending. Then protect that: put the table on its own sheet and lock it.
2. Start at the lowest possible value, usually 0 or a large negative number if the measure can go below zero. Otherwise the smallest values return #N/A.
3. No gaps. Each row's bound is where the previous band ends. Bands defined as "50 to 59" invite a value of 59.5 that belongs nowhere.
4. Exact match is a different function. If the key is a category rather than a number, you want MATCH(...,0), and the two must never be mixed up in one workbook.

Progressive brackets are not the same problem

A tax band that says "40 percent on the part above 50,270" is not a lookup, it is a cumulative calculation. A single approximate match gives you the rate that applies at the top; it does not give you the tax. For that you need a cumulative column in the band table:

=cum_tax + (income - lower_bound) * rate

With the three lookups pulling rate, lower_bound and cum_tax from the same matched row. An income of 60,000 gives 7,540 + (60,000 - 50,270) x 40 percent = 7,540 + 3,892 = 11,432. Those figures are illustrative; take the current thresholds from the tax authority, and keep them in the table where they can be updated in one place each April.

Why not nested IFs

The overlapping-branch failure is the same one that shows up in SQL, worked in CASE with overlapping conditions. A sorted band table cannot have that bug, which is a real argument for the table beyond tidiness.

How to apply this to your own work

1. Find your longest nested IF and count the cut-offs in it. That count is the number of rows in its replacement table.
2. Build the table on its own sheet, sorted ascending, starting at the lowest possible value.
3. Write the boundary policy in words next to it: does a value equal to the cut-off go up or down?
4. Rebuild the column with INDEX MATCH and reconcile every row against the old formula before deleting it.
5. Add a change log row with the date and who approved the cut-offs. Banded rules are exactly the thing people query six months later.

The one habit to keep

Put the rule where it can be reviewed. A cut-off inside a formula is a decision only its author can see, and the person who needs to check it is usually the one without access to the formula bar.

Where do your grade or tier boundaries live right now, and who could read them without asking you?