Written and reviewed by FormulaCraft Team. Each formula on this page is run through our verification engine before publishing.
Last reviewed:
How to do an approximate-match lookup in Excel and Google Sheets
Excel & Google Sheets
=VLOOKUP(A2,$C$2:$D$5,2,TRUE)Try it with your data
Edit the grid or formula, then run it through a real spreadsheet engine — no signup.
Sample data — click any cell to edit
Runs server-side · free · no signup
Step by step
- 1Sort your lookup table in ascending order by the first column — approximate match requires this or it will return wrong results.
- 2Use VLOOKUP with the last argument set to TRUE (or 1): =VLOOKUP(A2,$C$2:$D$10,2,TRUE). VLOOKUP scans down and returns the result for the largest key that is still <= the lookup value.
- 3Verify the table covers the full range of possible lookup values. Values below the smallest key in the table will return #N/A.
Tips
- Use MATCH with match_type 1 (ascending) instead of VLOOKUP when you want the row position rather than the value: =MATCH(score,bracket_column,1).
Working on a sheet you inherited? Run the Auditor on the whole file first — it flags every #REF!, #N/A, broken column pattern, and inconsistent formula in seconds, free, no signup.
Frequently asked
Why does approximate match VLOOKUP return wrong answers?
The most common cause is an unsorted lookup table. Approximate match VLOOKUP requires the first column to be sorted in ascending order. Sort the table and the results will be correct.