XLOOKUP for Rate Tables: Brackets, Tiers, and Effective Dates
Bracket lookups need match mode −1, and progressive tax needs more than a single lookup. Here are both, verified two ways on the same income, plus the SUMPRODUCT one-liner that replaces the whole bracket table.
Rate Tables Need Approximate Match
A rate table has bands: up to this amount, one rate; above it, another. Looking up $135,000 in a table whose rows start at $100,000 and $190,000 needs a function that returns the row at or below the value, not one that fails because $135,000 is not listed.
XLOOKUP does this with its fifth argument, match_mode, set to −1. That is the whole trick for band lookups. What comes after is the harder question of what to do with the rate once you have it.
Try the income tax calculatorCheck a bracket calculation against a full return before wiring it into a model.The five arguments and the two that matter
A progressive bracket table
This is an illustrative schedule, not a current tax table. Substitute the official figures for your jurisdiction and year before using it for anything real.
Swipe sideways to compare columns.
| Band floor | Rate | Cumulative tax at floor |
|---|---|---|
| $0 | 10% | $0 |
| $12,000 | 12% | $1,200 |
| $48,000 | 22% | $5,520 |
| $100,000 | 24% | $16,960 |
| $190,000 | 32% | $38,560 |
| $245,000 | 35% | $56,160 |
| $610,000 | 37% | $183,910 |
The cumulative column is the key. Tax on the first $100,000 is fixed at $16,960 regardless of what comes after, so a bracket calculation is that fixed amount plus the marginal rate on whatever exceeds the floor.
The one-line alternative
If you would rather not maintain a cumulative column, one SUMPRODUCT computes the same answer from the floors and the rate increments.
Both give $25,360, which is worth knowing because these two forms are a genuine check on each other. Build one, verify with the other, then keep whichever your colleagues will be able to read.
The same pattern elsewhere
Swipe sideways to compare columns.
| Table | Progressive or flat? | Formula shape |
|---|---|---|
| Income tax brackets | Progressive | Cumulative plus marginal |
| Shipping by weight band | Flat within band | One XLOOKUP |
| Volume discount tiers | Usually flat, sometimes progressive | Check the contract wording |
| Sales commission tiers | Usually progressive | Cumulative plus marginal |
| Utility usage blocks | Progressive | Cumulative plus marginal |
| Insurance rating bands by age | Flat within band | One XLOOKUP |
The distinction is worth reading carefully in the source document. A volume discount that says "orders over 500 units receive 12%" is flat, and the whole order gets 12%. One that says "the portion above 500 units receives 12%" is progressive, and needs the cumulative treatment. The two differ by thousands on a large order.
Rates that change over time
A rate table with an effective date column is the same problem in a different axis: find the most recent rate that was in force on or before the transaction date.
Why not VLOOKUP with TRUE
VLOOKUP with a TRUE fourth argument does the same approximate match, and plenty of working models use it. XLOOKUP is better on four specific counts rather than as a matter of taste.
Swipe sideways to compare columns.
| VLOOKUP with TRUE | XLOOKUP with −1 | |
|---|---|---|
| Lookup column position | Must be leftmost | Anywhere |
| Inserting a column | Silently breaks the column index | Unaffected |
| Unsorted data | Wrong answer, no error | Correct answer |
| Not found | #N/A, or a wrong nearby value | Your own message via if_not_found |
| Default behaviour | Approximate if the argument is omitted | Exact if the argument is omitted |
The third row is the dangerous one. VLOOKUP with TRUE on unsorted data returns an answer rather than an error, and that answer is arbitrary. It is the classic silent failure in financial models, and it survives review because nothing looks wrong.
Try the commission calculatorModel tiered commission where the tiers are progressive rather than flat.What the lookup does not handle
- Whether your bracket table is correct or current. The formula is exactly as right as the numbers you typed, and tax tables change annually.
- Deductions, credits, and phase-outs, which sit outside the bracket arithmetic entirely and often matter more than the marginal rate.
- Filing status. Every jurisdiction with progressive rates has several schedules, and picking the wrong one is a larger error than any formula bug.
- Additional levies stacked on top: surtaxes, payroll taxes, and local income taxes each have their own base and their own bands.
- Whether the tiers in a commercial contract are progressive or flat. That is a reading comprehension question, and the sheet cannot answer it.
- Rounding rules, which for tax are usually specified and are sometimes to the nearest dollar at a particular step rather than at the end.
What does match mode −1 do in XLOOKUP?
It returns an exact match if one exists, and otherwise the largest value that is smaller than what you looked up. That is exactly what a table of band floors needs: $135,000 finds the $100,000 row.
Why is income times the marginal rate wrong?
Because a progressive system taxes each band at its own rate, not the whole income at the top one. On $135,000 the correct tax is $25,360 and income times 24% gives $32,400, an overstatement of $7,040 or 28%.
Does XLOOKUP need sorted data?
Not for correctness with match modes 0, 1, and −1, unlike VLOOKUP with TRUE. Sorting is still worth doing for readability, and it is required if you use the binary search modes 2 and −2, which are faster on very large tables.
What if my table lists band ceilings instead of floors?
Use match_mode 1, which returns the next larger value. Be careful at the boundary: a table saying "up to $48,000" and one saying "from $48,000" treat exactly $48,000 differently, and that single value is where band-lookup bugs are found.
How do I return two columns from one lookup?
Give XLOOKUP a two-column return array and it spills both: =XLOOKUP(Inc, Floors, C2:D8, , −1) returns the rate and the cumulative tax side by side. This works in versions with dynamic arrays, and it is far cleaner than two separate lookups.
I do not have XLOOKUP. What is the equivalent?
INDEX with MATCH: =INDEX(Rates, MATCH(Inc, Floors, 1)). The 1 as the third MATCH argument is the approximate-match equivalent of match_mode −1, and it does require the floors sorted ascending.
Written by
Do The Calculation Team
Do The Calculation
Do The Calculation is built by a small team of data analysts and spreadsheet developers. Where a guide depends on a published formula, standard, or government rule, the calculator it links to names that source directly so you can check the number yourself.
About the team