21 Oct 2015

True Vlookup Tragedy

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

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  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”

…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) 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 )

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.

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

1. Great post about the VLOOKUP, Kevin!
I especially like your point: “getting a result from a formula doesn’t necessarily mean that the result is correct!”.
Keep up the good work!

1. Kevin Lehrbass says:

Thanks Mikkel !