Twice now this course has warned you about approximate matching: VLOOKUP’s treacherous default, MATCH’s silent one. Exact match, always — that was the rule, and for finding things it stays the rule.

But there’s a family of questions where approximate match isn’t a trap — it’s the entire point. Which grade does 74 fall into? Which commission tier does £8,200 earn? Which postage band is 2.3kg? These aren’t “find this value” questions; they’re “which band contains this value?” questions — and banding is what approximate matching was built for.

The shape: a threshold table

Every banding problem starts the same way: a small two-column table, sorted ascending, where each row says “from this value upward, this answer”:

From Tier
0 Bronze
5,000 Silver
10,000 Gold
25,000 Platinum

Then the lookup, in modern form:

=XLOOKUP([@Sales], Tiers[From], Tiers[Tier], , -1)

The -1 is the whole lesson: “exact match, or the next smaller.” £8,200 isn’t in the From column, so the lookup slides down to 5,000 — Silver. Read it as “which threshold did this value clear?” and the mode stops feeling dangerous and starts feeling inevitable.

£8,200 slides to the rung it cleared 25,000 → Platinum too high 10,000 → Gold too high 5,000 → Silver ✓ cleared 0 → Bronze not reached = "Silver" next-smaller match: the banding machine
Approximate match answers "which threshold did it clear?" — which is exactly what grades, tiers and tax bands ask.

Why this beats the IFS ladder

You met banding once before, as an IFS ladder, and the advice there stands: past a few rungs, the logic wants to be a table. The threshold-table version wins on every axis that matters — thresholds are data someone can see and edit (in a Table, so new tiers just work); the formula never changes when the policy does; and the sheet documents the scheme instead of burying it in nested conditions. When commission tiers change in January — they always change in January — someone edits four cells, not four hundred formulas.

The legacy dialects you’ll inherit do the same job: VLOOKUP(..., TRUE) and MATCH(..., 1) are next-smaller matchers too, and old-timers’ =LOOKUP(x, table) is the same machine again. Now you can read them — and know that their one absolute requirement is yours as well.

The two disciplines

Sorted, ascending, always. Approximate match walks the thresholds assuming order; an unsorted table returns confident nonsense, not errors. Guard it: thresholds live in their own little Table, sorted, ideally on the inputs sheet — never scattered mid-data where a stray sort can shuffle policy.

Mind the edges. Bands are >= on the From value — a 74.9 with a “75 = A” threshold is a B, and whether that’s right is policy, not maths. Test the exact boundary values, and values below the first threshold (XLOOKUP returns #N/A if nothing was cleared — often the correct scream; add a 0 row when it isn’t).

The graduation: marginal bands

One caution completes the lesson. Banding answers “which tier does the whole value sit in” — flat-rate schemes, grades, postage. Marginal schemes — income tax, where each slice pays its own rate — need the slice-by-slice model instead; applying a banded rate to a whole salary is the classic way to overstate someone’s tax by thousands. Which question is your scheme asking? That’s stage-four maturity in one line: knowing what the lookup means before writing it.

Sorted table, -1, test the edges. The mode you were warned about, doing the one job it was always meant to do.