Subscriber only lesson.
Sign up to this course to view this lesson.
About this lesson
Learn to use VLOOKUP to find an approximate match and return it or the corresponding value from another column.
Download this lesson’s related exercise files.VLOOKUP with Approximate Match.xlsx
14.5 KB VLOOKUP with Approximate Match - Completed.xlsx
VLOOKUP (approximate match).
Using VLOOKUP with an approximate match to return the closest value in a column without going over.
Where/when to use the technique
Using VLOOKUP with an approximate match is ideal for tables that have gaps in data, such as volume pricing or tax tables.
VLOOKUP’s parameters (in English)
- =VLOOKUP([what should it look for],[where is the table to look in],[which column should it return a value from],[guess if it can’t find a match])
Key points with VLOOKUP and approximate matches
- For an approximate match, VLOOKUP’s final parameter must be set to TRUE.
- The first column of your data MUST be sorted in ascending order. (If it isn’t, you will get very strange results.)
- The column to return is the column of the table, not the column of the spreadsheet (if your VLOOKUP table starts in column B, then 1 refers to column B, as it is the first column in the table.)
- VLOOKUP will return the first row that is not in excess of what you’re looking for.
- It is a best practice to declare the final parameter as TRUE, even though it defaults. (This will get you in the habit of declaring the last parameter, making it less likely to forget it when it needs to be FALSE).
- =VLOOKUP(5000,A1:B10,2,TRUE) Returns the value from the second column of the table where the record in column A is closest to 5000 without going over.
Lesson notes are only available for subscribers.