Learn Excel
EN
Sets how numbers, dates and amounts look in the exercises, and whether you type , or ; between arguments, just like your own Excel.

Home › Excel functions › VLOOKUP › Approximate match

VLOOKUP Approximate Match: How TRUE Works

Most of the time you want VLOOKUP to find an exact match. But for discounts, shipping costs, tax brackets or grades, your value usually falls somewhere between two rows of the table. That's what approximate match is for: VLOOKUP finds the band your value belongs to.

VLOOKUP · 2 min read · Try it yourself ↓
In this guide
  1. How approximate match works
  2. The table must be sorted
  3. Try it yourself
  4. More examples
  5. Exact vs. approximate
  6. More guides
  7. FAQ

How approximate match works

Approximate match is the fourth argument of VLOOKUP set to TRUE, or left out:

=VLOOKUP(D2, A2:B6, 2, TRUE)

VLOOKUP then goes down the first column and stops at the last value that is not larger than your lookup value. It returns the value from that row. Take this discount table:

ABCDE
1Quantity fromDiscountQuantityDiscount
210%3610%
3105%
42510%
55015%
610020%

For a quantity of 36, VLOOKUP passes 1, 10 and 25, sees that 50 is too large, and goes back to the row with 25. The discount is 10%. Every quantity from 25 up to 49 gets 10%, and from 50 it's 15%.

Each row is the start of a band. Write the lowest value of every band in the first column: 1, 10, 25 and so on. You don't need an "up to" column: the next row is where the band ends.

The first column must be sorted

Approximate match only works if the first column is sorted from small to large. VLOOKUP doesn't check every row; it assumes the order is right and stops early. On an unsorted table it returns a wrong value, and it doesn't warn you.

Also make sure the first row covers the smallest possible value. If someone orders 0 items and your table starts at 1, VLOOKUP finds nothing smaller and returns #N/A. Starting the table at 0 prevents that.

Practice: VLOOKUP with approximate match

Look up the discount for an order of 36 items. The tips walk you through each part of the formula.

Exercise: in cell E2, write =VLOOKUP(D2,A2:B6,2,TRUE) to find the discount. Turn on JavaScript to try it interactively.

Download the practice file (.xlsx)8 more VLOOKUP exercises →

More examples

Grades from scores. A table with 0, 50, 60, 70 and 85 in the first column and F, D, C, B and A next to it. =VLOOKUP(B2, $E$2:$F$6, 2, TRUE) turns a score of 78 into a B.

Shipping by weight. Weight bands starting at 0, 2, 5, 10 and 20 kg, with a price per band. A parcel of 7 kg gets the price of the 5 kg band.

Tax brackets. Income thresholds in the first column and the rate next to them. For a progressive tax, where each band has its own rate, you need more than one lookup, but for a flat rate per bracket a single VLOOKUP is enough.

Exact match or approximate match?

Exact match (FALSE)Approximate match (TRUE)
Use forNames, codes, IDs, productsBands: quantities, amounts, scores, dates
Sorting neededNoYes, small to large
Value not found#N/AThe band below it
DefaultNo, you must type FALSEYes, when you leave the argument out

Leaving out the last argument means TRUE. If you look up names or codes and forget FALSE, VLOOKUP uses approximate match and can return a value from the wrong row. Always type FALSE unless you really want bands.

In Excel 365 you can also use XLOOKUP with match mode -1 ("exact match or next smaller item"). It does the same as approximate match, but works on unsorted data too.

More VLOOKUP guides

The complete guideFind a value in a table and return a matching value from the same row.Practice exercises8 exercises with tips: exact and approximate match, IFERROR, two conditions and another sheet.From another sheetPull data from a list on another tab or in another workbook.Multiple criteriaLook up a value that matches two conditions, like product and size.#N/A errorWhy VLOOKUP can't find your value, and how to fix or hide #N/A.

Frequently asked questions

What does TRUE mean in VLOOKUP?

TRUE asks for an approximate match: VLOOKUP returns the row with the largest value that is not larger than your lookup value. FALSE asks for an exact match.

Is approximate match the default in VLOOKUP?

Yes. If you leave out the fourth argument, VLOOKUP uses approximate match. That's why it's good practice to always type FALSE when you want an exact match.

Why does VLOOKUP approximate match return the wrong value?

Almost always because the first column isn't sorted from small to large. Sort the table on that column and the results are correct.

Can I use approximate match with text?

It works alphabetically, but it's rarely useful: "Banana" would match the row for "Apple" if "Banana" itself is missing. For text, use FALSE.