Approximate match is for ranges, not exact values: grades, tax slabs, discount bands and commission tiers. Here the lookup value does not need to appear in the table.
How it works
With TRUE (or the last argument left out), VLOOKUP finds the largest value that is less than or equal to your lookup value. This only works if the first column is sorted ascending.
Example: marks to grade
Build a small table in E2:F6:
- 0 → F
- 40 → D
- 55 → C
- 70 → B
- 85 → A
Then use: =VLOOKUP(B2, $E$2:$F$6, 2, TRUE)
A score of 72 returns B, because 70 is the largest value that is not more than 72.
Example: commission tiers
Put sales thresholds (0, 50000, 100000) in one column and the percentage in the next. The same formula returns the correct rate for any sales amount.
TRUE vs FALSE
- FALSE: exact match only. Use it for IDs, names and codes.
- TRUE: range match. Use it for bands and slabs.
Common mistakes
- The table is not sorted ascending, so results are wrong with no error shown.
- The table does not start at the lowest value (such as 0), which gives #N/A for small values.
- Using TRUE for IDs by accident.
If you prefer, IFS or XLOOKUP with match mode -1 can do the same job.