Tax Bracket and Tier Lookups in Excel

A tax bracket lookup in Excel finds which band an amount falls into and returns that band's rate. You list each bracket's lower limit with its rate, then use VLOOKUP with TRUE or XLOOKUP with match mode -1. This guide builds the lookup step by step, then shows how to turn the rate into a real progressive tax figure, and how the same method handles any tiered table.

The brackets in this guide are a simple made-up example, not any country's real tax rules. Swap in your own figures.

How to set up a bracket table#

A bracket table has one row per band, with the lower limit of the band in the first column. Sort it from smallest to largest and start at 0:

Read each row as "from this amount up to the next limit". So income from 40,000 up to 89,999.99 is in the 20% band. You don't need a column of upper limits; the next row's lower limit does that job.

The 0 row matters. Without it, any income below 10,000 has no bracket and the lookup returns #N/A.

Look up the bracket with VLOOKUP TRUE#

VLOOKUP with TRUE as the last argument does an approximate match: it finds the largest lower limit that is less than or equal to the amount. With income in B2:

=VLOOKUP(B2,D2:E5,2,TRUE)

For an income of 55,000, VLOOKUP doesn't find 55,000, so it takes the last limit that isn't bigger: 40,000. The result is 20%.

The table must be sorted smallest to largest. If it isn't, VLOOKUP can return a wrong rate without any error. The VLOOKUP function guide covers the TRUE and FALSE options.

Look up the bracket with XLOOKUP#

XLOOKUP does the same job with match mode -1, which means "exact match or the next smaller item":

=XLOOKUP(B2,D2:D5,E2:E5,,-1)

This also returns 20% for 55,000. The two commas leave the not-found argument empty. XLOOKUP has two advantages here: the table doesn't need to be sorted, and the rate column can sit anywhere, even to the left. It needs Excel 2021 or Microsoft 365. See the XLOOKUP function guide for all its arguments.

Dates work the same way. A price list with "effective from" dates is just a bracket table where the limits are dates; see how to look up a value by date.

Bracket rate vs progressive tax#

The rate from the lookup is the rate on the top slice of income, not the rate on all of it. In a progressive system, each slice is taxed at its own rate. For an income of 55,000 with the table above:

So the tax is 6,000, not 20% of 55,000 (11,000). Mixing these up is a common mistake in finance tests.

Calculate progressive tax with a base-tax column#

The cleanest method adds a third column: the tax due on all income below each lower limit.

Each base tax is the previous base plus the previous band taxed in full. For the 90,000 row: 3,000 + (90,000 - 40,000) x 20% = 13,000.

Now tax = base tax + (income - lower limit) x rate:

=VLOOKUP(B2,D2:F5,3,TRUE)+(B2-VLOOKUP(B2,D2:F5,1,TRUE))*VLOOKUP(B2,D2:F5,2,TRUE)

For 55,000: 3,000 + (55,000 - 40,000) x 20% = 3,000 + 3,000 = 6,000, which matches the slice table. Each VLOOKUP finds the same row and returns a different column.

Calculate progressive tax with SUMPRODUCT#

If you'd rather not keep a base-tax column, SUMPRODUCT can do it with a column of rate increases: how much each band's rate goes up from the one before. Here that is 0%, 10%, 10% and 10% in G2:G5:

=SUMPRODUCT((B2>D2:D5)*(B2-D2:D5)*G2:G5)

For 55,000, this adds 55,000 x 0% + 45,000 x 10% + 15,000 x 10% = 0 + 4,500 + 1,500 = 6,000. The (B2>D2:D5) part switches off bands the income doesn't reach. This formula is compact, but the base-tax version is easier for others to check.

Use the same lookup for any tiered table#

Any table where values fall into ranges works the same way: commission tiers, volume discounts, grade boundaries, shipping bands. The rules don't change:

1. Put the lower limit of each tier in the first column, starting with the smallest possible value.
2. Sort ascending if you use VLOOKUP TRUE.
3. Use TRUE (VLOOKUP) or -1 (XLOOKUP), never an exact match.

If the tier depends on two things, such as region and amount, see lookups with multiple criteria. To find the most recent rate rather than a band, read how to look up the last match. And if the rate column sits left of the limits, lookups to the left explains your options.

Common mistakes with bracket lookups#

• FALSE instead of TRUE. An exact match returns #N/A for almost every income, because few incomes equal a limit exactly.
• Upper limits in the first column. A table that lists "up to 10,000" values puts everyone one band too low. Use lower limits, or XLOOKUP with match mode 1.
• An unsorted table with VLOOKUP. Sort it ascending, or switch to XLOOKUP.
• Rates typed as whole numbers. 20 instead of 20% (0.2) makes every tax figure 100 times too big.

Practise it#

Bracket lookups are a favourite in finance and HR job tests. Practise VLOOKUP approximate match in a live spreadsheet: you type the formula and it checks the result instantly. Then practise XLOOKUP match modes to do the same with -1 and 1, and try the finance Excel mock test when you're ready.