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.
Multiple versions of this lesson are available, choose the appropriate version for you:
Download the ‘before’ and ‘after’ Excel workbooks from the video tutorial and try the lesson yourself.VLOOKUP with Approximate Match.xlsx
15 KB VLOOKUP with Approximate Match - Completed.xlsx
Using VLOOKUP to find an approximate match (not exceeding the value looked for) in a data table and return it or the corresponding value from another column.
When to use
Using VLOOKUP with an approximate match is ideal for tables that have gaps in data, such as volume pricing or tax tables.
- Create a new worksheet
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.