The vlookup function is often used to prove a bare minimum of Excel knowledge because it’s both useful and common.
Why ‘True vlookup tragedy’?
Scroll to the bottom of this post where you see this tragedy picture to read more. For now, I’ll teach you how vlookup works.
Do you learn by reading or watching?
To learn about the vlookup, read my post below. If you prefer to watch a video then consider the following:
- Video 00016 VLOOKUP introduction Careful step by step vlookup tutorial part 1
- Video 00017 VLOOKUP Introduction Part2 Careful step by step vlookup tutorial part 2
- Video 00050 VLOOKUP versus INDEX MATCH See how Index & Match functions team up!
- Video 00115 LOOKUPS and LETTERS in Excel Be careful doing vlookup with text!
What does a vlookup do?
The vlookup (the ‘v’ stands for vertical) allows you to look for a value in a column and then return a value found somewhere to the right. It’s similar to looking for someone’s name in the telephone book and then looking to the right to get their phone number or address.
Consider this example
We want to look for the number 7 in column A and if found return a value in column B. If there is no 7 than we expect no answer (Excel will give us an #N/A error meaning not applicable or not found).
As you can tell by looking at the screenshot to the left, there is no number 7 in the range A1 to A5.
The vlookup function has 4 parts (or arguments) but the 4th part is optional. If you omit the 4th part and Excel can’t find an exact match then Excel may return what it thinks is the closest match (more on this later on). In this case, as we are looking for exactly a 7, we’ll use false or 0 (zero). If you’re looking for 7 (and only a 7) then the correct formula is: =vlookup(L1,$A$1:$B$5,2,false)
Let’s review each part of the formula.
Note that each part of the formula is separated by a comma (or possibly a semicolon depending on your region).
Once you have written and entered the formula you can click inside the formula bar to see the function component descriptions.
- Part 1 Lookup value L1 we are looking for the value in cell L1 (it’s a 7)
- Part 2 Table array $A$1:$B$5 range containing lookup value (column A) and answer (column B)
- Part3 col_index_num 2 indicates our answer i from the 2nd column (column A=1, column B=2)
- Part4 [range_lookup] false (or 0) tells Excel “if you don’t find the exact value don’t guess”
The answer to our vlookup is #N/A as the 7 is not found (If we had been looking for a 43 then the answer would be grape).
…and here comes the “true” tragedy…
Because the 4th component is optional, some would mistakenly write the formula like this: =vlookup(L1,$A$1:$B$5,2) This assumes that you want the closest match as Excel defaults to true if part 4 is omitted!
Forgetting to include the false or zero at the end of a vlookup is unfortunately a common error. I have pointed this out to many people over the years and the responses have varied from “I have been doing it wrong for over 10 years!!” to gasps to responses that I can’t even include here (imagine a large financial model that spits out incorrect results!).
I’ll add that this formula =vlookup(L1,$A$1:$B$5,2,true) is the same as =vlookup(L1,$A$1:$B$5,2) Both of these formulas neglect the false or 0 indicating exact match.
Remember: getting a result from a formula doesn’t necessarily mean that the result is correct!
So, the next question I often get is “Why on earth would anyone want a closest match?”.
There are two reasons that I can think of:
- 1) A rare case in which the text is sorted alphabetically and you’re looking for the closet text match (this is very rare)
- 2) You have numbers (i.e. 0,100,200,300,400), typically in ascending order, and you want to bin a number within these numbers (a common and correct use of TRUE or 0 to determine which pair of numbers it falls between)
Other Common Vlookup Errors
- #REF! error. If your table array is $A$1:$B$5 then the ‘column index number’ can only be a 1 or 2 (width of range is 2 columns)
- Data issues (you’re lookup value is “school” but in your data you have “school “)
- You forgot to lock the ‘Table array” before dragging down the formula (i.e. locked has $ like this: $A$1:$B$5 )
Additional Free Vlookup Blog Posts
- Vlookup explained by Microsoft
- Vlookup examples from Debra Dalgleish
- Chandoo’s vlookup week
- Jon Acampora explains vlookup using Starbucks menu
- Jon Acampora compares vlookup with index/match
- Charley Kyd’s lookup article (index/match, vlookup, hlookup)
Other lookup related videos
My name is Kevin Lehrbass. This is my personal blog about Microsoft Excel. I live in Markham, Ontario, Canada. I’ve been studying, supporting, building, troubleshooting, teaching and dreaming in Excel since 2001. I’m a Data Analyst at McKinsey & Company.
Away from Excel I enjoy playing with my dog Cali, learning Spanish, playing Chess and drawing nerds.