VLOOKUP Approximate Match for Tiers and Bands

A VLOOKUP approximate match puts a number into the right band: a commission tier, a tax bracket, a grade or a discount level. You end the formula with TRUE instead of FALSE, and VLOOKUP returns the row with the largest start value that is less than or equal to your number. This guide is for anyone who already knows a basic exact-match VLOOKUP and now needs to work with tiers.

What does VLOOKUP approximate match do?#

It finds the band your number falls into, not an exact copy of your number. A normal lookup with FALSE needs the value to be in the table. A tier table lists only where each band starts, so most numbers you look up are not in it.

The syntax is the same as any VLOOKUP, with TRUE at the end:

=VLOOKUP(lookup_value, table_array, col_index_num, TRUE)

• lookup_value: the number you want to place, like a sales total.
• table_array: the tier table. Its first column holds the start of each band.
• col_index_num: which column of the table to bring back (1 is the first column).
• TRUE: approximate match. VLOOKUP walks down the first column and stops at the last value that is not bigger than your number.

If you are new to the function itself, start with our beginner's guide to VLOOKUP or the VLOOKUP function guide.

How to set up a tier table#

A tier table needs one row per band, with the lowest value of each band in the first column, sorted from smallest to largest. Here is a commission table in F1:H5:

Read each row as "from this amount up to the next one". Silver covers 25,000 up to just under 50,000. Platinum has no upper limit. Always start the first band at 0 (or the lowest number you could ever look up), so every value has a band.

VLOOKUP approximate match example: commission tiers#

Use the same four parts as always, and change only the last one to TRUE. The sales reps are in column A and their quarterly sales in column B. In C2, type:

=VLOOKUP(B2,$F$2:$H$5,3,TRUE)

• B2 is Maya's sales, 62,000.
• $F$2:$H$5 is the tier table, locked with $ signs so it doesn't move when you fill down.
• 3 brings back the Tier column.
• TRUE asks for an approximate match.

62,000 is not in column F. VLOOKUP passes 0, 25,000 and 50,000, then sees 100,000, which is too big. It steps back to 50,000 and returns Gold. Fill down with Ctrl+D (Mac: ⌘D) and you get:

Look at Tom and Ken. Tom's 50,000 matches the start of Gold exactly, and "less than or equal to" puts him in Gold. Ken's 24,999 is one dollar short of Silver, so he stays in Bronze.

For the rate, change only the column number: =VLOOKUP(B2,$F$2:$H$5,2,TRUE). The result is a number, so you can use it in a calculation. The commission in dollars is =B2*VLOOKUP(B2,$F$2:$H$5,2,TRUE), which gives 3,720 for Maya (62,000 × 6%).

Why must the table be sorted?#

Approximate match expects the first column in ascending order (smallest first). It doesn't check every row. It searches as if the list is in order, so an unsorted table can give the wrong band with no error at all.

If a coworker sorts the tier table with Platinum at the top, your formulas may still show tiers, but some of them will be wrong. This is the most dangerous kind of spreadsheet mistake, because nothing looks broken. Before you trust an approximate lookup, check two things:

1. The first column goes from smallest to largest.
2. There are no blank rows or text values in the first column.

A number smaller than the first band has nowhere to go, so VLOOKUP returns #N/A. With a table that starts at 25,000, a sale of 18,500 gives #N/A. The fix is to add a 0 row at the top.

TRUE, FALSE or nothing: the 4th argument#

The 4th argument decides the match type, and leaving it out means TRUE. These three formulas show the difference:

The default causes many silent errors in normal lookups. If you look up a product code and forget FALSE, VLOOKUP does an approximate match on an unsorted list and can return another product's price. Always type the 4th argument, even when you want TRUE, so the next person can see what you meant. You can also write 1 for TRUE and 0 for FALSE.

Approximate match with XLOOKUP or INDEX MATCH#

XLOOKUP can do the same job with match_mode -1 (exact match or the next smaller value):

=XLOOKUP(B2,$F$2:$F$5,$H$2:$H$5,,-1)

The two commas leave the 4th part, if_not_found, empty. Unlike VLOOKUP with TRUE, this doesn't need a sorted table. XLOOKUP needs Excel 2021 or Microsoft 365, so use VLOOKUP when the file must open in older versions.

INDEX and MATCH also work. MATCH with a match type of 1 finds the largest value less than or equal to your number, and it needs the same ascending sort:

=INDEX($H$2:$H$5,MATCH(B2,$F$2:$F$5,1))

To learn how these two functions work together, read INDEX MATCH in Excel: the beginner's guide. If your bands run across a row instead of down a column, HLOOKUP works the same way with TRUE. And when you need to match a row and a column at once, see the two-way lookup guide.

Common mistakes with approximate match#

• Listing the end of each band instead of the start. VLOOKUP needs the lowest value of each band. If your table says "up to 25,000", rewrite it as "from 0", "from 25,000" and so on.
• Forgetting the $ signs. Without $F$2:$H$5, the table slides down as you fill and the last rows return #N/A or wrong tiers.
• Using TRUE for codes or names. Approximate match on text returns a "close" item, not the one you asked for. Use FALSE for anything that must match exactly.
• Numbers stored as text. If the sales figures came from another system as text, VLOOKUP can't compare them with the numbers in the table. Convert them with VALUE first.

Practise it#

The fastest way to make this stick is to build a tier lookup yourself and watch the edge cases. In ExcelReady's VLOOKUP approximate match lesson, you place sales reps into commission tiers, lock the table, fill down, and work out each rep's commission in dollars, all in a live spreadsheet in your browser. Try the grade-band exercise on this page first, then practise approximate VLOOKUP on commission tiers.