Approximate match lookup: tax bands, grades and discounts

An approximate match lookup finds the band a number falls into. It returns the row with the largest value that is not above your number. In VLOOKUP, the last argument is TRUE. In XLOOKUP, the match mode is -1.

Here is a wholesaler's discount table. Orders of 50 or more get 5%. Orders of 100 or more get 8%, and so on. No order will match a row exactly, and that is the point.

Column E holds the lowest quantity of each band, from small to large. Column C is the answer the formula gives. Columns C and F show percentages.

The formula

Take order 1004. It is for 260. There is no 260 in column E. The largest bound that is not above 260 is 250. So the lookup returns 12%.

The dollar signs lock the table, so it does not move when you fill down. The article on dollar signs shows how they work.

Bounds, and numbers past the end

• A number on a bound goes into that band. Order 1002 is for exactly 50, so it gets 5%. Order 1003 is for 99 and also gets 5%.
• Above the last bound, you get the last band. Order 1005 is for 1200 and gets 15%. The last band has no top.
• Below the first bound, you get #N/A. Say the table starts at 1, not 0. An order for 0 gives #N/A, with VLOOKUP and with XLOOKUP.

So start the table at the lowest value that can happen, usually 0. If the last band should have a top, add a row that says so, like "Call us" from 5000.

The sort rule

VLOOKUP with TRUE needs the first column sorted from smallest to largest. It does not check. On a table that is not sorted, it can return the wrong band. There is no error, and the wrong answer looks like a real discount.

XLOOKUP with -1 does not need sorted data. It reads every row and picks the largest value that is not above your number. Say the bounds are in the order 100, 0, 500, 50, 250. XLOOKUP still gives 5% for 99 and 12% for 260.

There is one exception. XLOOKUP has a sixth argument, the search mode. A search mode of 2 or -2 is a fast search that needs sorted data. You only get that if you type it.

Sort a band table anyway. People read it too, and a sorted table is easy to check. And the file may one day be opened in Excel 2019, where XLOOKUP does not exist.

Grades: the same lookup, with text

A band lookup can return text. Here is a grade table in H1 to I5:

The lookup does not round. 49.5 is below 50, so it is a Fail. If your rules round scores first, put ROUND around B2 inside the lookup.

Tax bands: the lookup gives a rate, not the tax

Many tax systems charge each part of an income at its own rate. The bands below are made up. Income up to 12000 is taxed at 0%. The part from 12000 to 40000 is taxed at 20%. The part above 40000 is taxed at 40%.

The table is in J1 to L4. The income is in B2. Take an income of 55000.

The mistake: =B2*VLOOKUP(B2,$J$2:$L$4,2,TRUE). It gives 22000. That is 40% of all 55000, and only the top part should pay 40%.

A discount is different. There, the whole order usually gets one rate, so the simple lookup is right. Check which rule your table follows before you build it.

LOOKUP does the same thing

=LOOKUP(B2,$E$2:$E$6,$F$2:$F$6) is an approximate match too. For 260 it gives 12%. It works in every version of Excel. It follows the same sort rule as VLOOKUP with TRUE.

Questions people ask

What if I leave out TRUE?

VLOOKUP with no fourth argument is an approximate match. That is right for a band table. For codes and names, it gives wrong answers. So type FALSE for exact lookups. The XLOOKUP vs VLOOKUP article compares the defaults.

Why did FALSE give #N/A on my band table?

FALSE asks for an exact match. 260 is not in column E, so there is nothing to find. Change it to TRUE.

Should I use XLOOKUP or VLOOKUP here?

Use XLOOKUP with -1 unless the file must open in Excel 2019 or older. It does not depend on the order of the table. Then sort the table anyway.

You can practise band lookups in the lessons below. Each lesson is a real sheet you type into, and each answer is checked when you press Enter.