# 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.

---

- **Canonical URL:** https://dothecalculation.com/blog/templates/excel-xlookup-rate-tables
- **Category:** Templates
- **Author:** Do The Calculation Team
- **Published:** 2026-08-03
- **Reading time:** 11 min read
- **Publisher:** Do The Calculation (https://dothecalculation.com)
- **Methodology:** https://dothecalculation.com/methodology

---

## 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.

Tool: [Try the income tax calculator](https://dothecalculation.com/calculators/income-tax-calculator) — Check a bracket calculation against a full return before wiring it into a model.

## The five arguments and the two that matter

**XLOOKUP in full**

```
=XLOOKUP(lookup, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
```
- match_mode 0 is exact and is the default. This is the safe default and the wrong one for bands.
- match_mode −1 returns an exact match or the next smaller item. This is what band tables need.
- match_mode 1 returns an exact match or the next larger item, which suits "up to and including" tables.
- search_mode −1 searches from the bottom up, which is how you find the most recent effective-dated rate.

> **Which way your table is written decides the match mode** — A table listing band floors, the amount at which each rate starts, needs −1. A table listing band ceilings, the amount up to which each rate applies, needs 1. Getting this backwards puts every value in the neighbouring band, and the results still look plausible.

## 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.

**Floors, rates, and cumulative tax at each floor**
| 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.

**Progressive tax with three lookups**

```
=XLOOKUP(Inc, Floors, CumTax, , −1) + (Inc − XLOOKUP(Inc, Floors, Floors, , −1)) * XLOOKUP(Inc, Floors, Rates, , −1)
```
- On $135,000: 16,960 + (135,000 − 100,000) × 24% = 16,960 + 8,400 = $25,360
- The marginal rate is 24%; the effective rate is 25,360 ÷ 135,000 = 18.8%.
- A single XLOOKUP returning the rate and multiplying by income would give $32,400, overstating the tax by $7,040.

> **The mistake this formula exists to prevent** — Income × marginal rate is the most common spreadsheet error in personal finance. It taxes every dollar at the top rate. On $135,000 it overstates by 28%, and it gets worse the higher the income, because more of it sits in bands below the top one.

## 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.

**Brackets in one formula**

```
=SUMPRODUCT(--(Inc>Floors), (Inc−Floors), RateDeltas)
```
- RateDeltas is the increase in rate at each band: 10%, 2%, 10%, 2%, 8%, 3%, 2%.
- On $135,000: 135,000×10% + 123,000×2% + 87,000×10% + 35,000×2% = 13,500 + 2,460 + 8,700 + 700 = $25,360
- Same answer, no cumulative column to maintain, and harder for a reader to follow.

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

**Band lookups that come up constantly**
| 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.

**The rate in force on a date**

```
=XLOOKUP(TxnDate, EffectiveDates, Rates, "No rate in force", −1)
```
- Sort the effective dates ascending, and match_mode −1 returns the latest date on or before the transaction.
- The if_not_found string catches a transaction predating the first rate, which is a data problem worth seeing.
- Never delete a superseded rate row. Historical transactions need the rate that applied then.

## 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.

**The four practical differences**
|  | 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.

Tool: [Try the commission calculator](https://dothecalculation.com/calculators/commission-calculator) — Model 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.

---

_Source: [Do The Calculation](https://dothecalculation.com/blog/templates/excel-xlookup-rate-tables). Quote freely with attribution and a link to this page._