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’?

Vlookup is often not used properly and is a common cause of errors.

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 vlookup, read my post below. If you prefer to watch a video then consider these:

• 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). 

In the formula bar type  =vlookup(   to see vlookup’s 4 components

• 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!

Closest Match?

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) Rare case in which the text is sorted alphabetically and you’re looking for the closet text match
• 2) You have numbers (i.e. 0,100,200,300,400), in ascending order. You want to bin a number within these numbers (a common and correct use of TRUE 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 ‘column index number’ can only be a 1 or 2
• Data issues (your 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

• What’s wrong with my vlookup?
• Dynamic Lookup Column and Answer Column

Subscribe to my YouTube channel and learn more!

About Me

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.

Thousands of hours can be saved and costly errors can be avoided or corrected if you study this powerful software. Check out my videos and blog posts.

Away from Excel I enjoy playing with my dog Cali, learning Spanish, playing Chess and drawing nerds.

Save

Save