XLOOKUP approximate match

Here's the one place where approximate matching is the whole design, and it stops being a hazard. Banding problems all share a shape. Think of income to tax slab, monthly sales to commission tier, order size to volume discount, or score to grade. The key is a continuous number, and you want the band it falls into. Exact match is useless here, because nobody's income is exactly a slab boundary. What you want is "the highest band threshold that my value still exceeds".

How do you use approximate match safely?

The non-negotiable safety rule: approximate match is only correct on a table whose lookup column is sorted ascending and built as lower bounds of each band. On unsorted data approximate match returns garbage and gives you no warning at all. It's the deadliest bug you'll meet in lookups, and it gets the signature warning in Common mistakes. Sort first, always.

Two ways to do it correctly:

• XLOOKUP with match_mode -1, which means "exact or next smaller". It returns the value whose lookup-array entry is the largest one still ≤ your key. Against a lower-bound table, that's the band your key falls in. XLOOKUP scans, so it tolerates an unsorted table for -1/1. Sort it anyway. It reads better, and it lets you switch on binary search_mode 2 for speed.
• INDEX + MATCH with match_type 1. MATCH(key, bounds, 1) returns the position of the largest bound ≤ key. This one requires an ascending sort, because MATCH uses a binary search and will return nonsense on unsorted data. It's the classic pre-XLOOKUP idiom, and it's still perfectly valid.

Worked example: progressive tax slabs

Build the lower-bound table once. A progressive tax needs three columns: the lower bound, the marginal rate in that band, and the cumulative tax already accrued at the moment you cross into that band.

     A              B          C
1    Lower bound    Rate       Cumulative tax at bound
2    0              0%         0
3    300000         5%         0
4    700000         10%        20000
5    1000000        15%        50000
6    1200000        20%        80000
7    1500000        30%        140000

There's nothing magic about column C. Each entry is the tax fully accrued on all the lower bands. At the ₹7,00,000 bound you've already paid 5% on the ₹3 to 7 lakh band, which is ₹20,000. At ₹10,00,000 you add 10% × ₹3,00,000 = ₹30,000 to reach ₹50,000, and so on. Worked example 1 recomputes every figure. With this table in place, the full progressive tax for an income in E2 is one formula:

=XLOOKUP(E2, A2:A7, C2:C7, , -1) + XLOOKUP(E2, A2:A7, B2:B7, , -1) * (E2 - XLOOKUP(E2, A2:A7, A2:A7, , -1))

Read it in three pieces: cumulative tax at my band's floor + my band's marginal rate × the income above that floor. The three XLOOKUPs all use match_mode -1 to land in the right band. The INDEX + MATCH twin is:

=INDEX(C2:C7, MATCH(E2, A2:A7, 1)) + INDEX(B2:B7, MATCH(E2, A2:A7, 1)) * (E2 - INDEX(A2:A7, MATCH(E2, A2:A7, 1)))

Both return the same progressive tax. Hold on to the pattern, floor amount plus marginal rate times the excess, because every tiered calculation in finance is built this way, from tax to tariff to broker commission.