The guide · VLOOKUP approximate match
How TRUE works
A bands table lists where each band starts: 0 is an F, 50 a D, 60 a C, 75 a B, 90 an A. With TRUE as the last argument, VLOOKUP goes down the first column and stops at the last value that isn't bigger than what you're looking for. For a score of 82 that's 75, so the answer is B. A score of exactly 75 is also a B: the start of a band belongs to that band.
Rules for the table
- Sorted from small to large. Approximate match assumes it. An unsorted table gives wrong answers without an error.
- The first row is the lowest possible value. Start at 0. A score below the first row has no band and gets #N/A.
- One row per band start, not per band end. "50–59" is written as 50.
TRUE or FALSE?
FALSE (exact) | TRUE (approximate) | |
|---|---|---|
| Looks for | exactly the same value | the band the value falls into |
| Table must be sorted | no | yes, smallest first |
| Use it for | names, codes, IDs | grades, tax brackets, tiers |
| When there's no match | #N/A | #N/A only below the first row |
Leaving the last argument out is the same as TRUE. Write it anyway, so the next person can see you meant it.
The XLOOKUP version
In Excel 365 and 2021 or later, =XLOOKUP(B2, $E$2:$E$6, $F$2:$F$6, , -1) does the same thing. Match mode -1 means "exact match, or the next smaller value". Without it, XLOOKUP only does exact matches.
Common mistakes
- FALSE on a bands table. 82 isn't in the table, so an exact match gives #N/A. Only scores that land exactly on a band start work, which makes the mistake easy to miss.
- A table that slides when you fill down. Lock it as
$E$2:$F$6, or the lower rows lose the first bands. - Band ends instead of band starts. A table of 49, 59, 74… puts every score in the band above.
FAQ
- What does TRUE mean in VLOOKUP?
- TRUE asks for an approximate match: VLOOKUP returns the row with the largest value that is less than or equal to the lookup value. The first column must be sorted from smallest to largest.
- Why does VLOOKUP return #N/A for a number in a range?
- The formula uses FALSE, which needs the exact number to be in the table. For bands and brackets, use TRUE as the last argument.
- Does the table need to be sorted for approximate match?
- Yes, from smallest to largest in the first column. On an unsorted table, approximate match can return a wrong value without showing an error.
- What happens if I leave out the last argument of VLOOKUP?
- VLOOKUP uses approximate match, the same as TRUE. That is right for bands, but on a list of names it can return the wrong row, so write FALSE there.