Software

Troubleshoot VLOOKUP() formula gotchas

Excel's VLOOKUP() function is notorious for returning wrong results, but it's not the function, it's the way you're using it!

Excel's VLOOKUP() function finds a matching value in a list. It's one of Excel's most popular and misunderstood functions. It's possible to enter the correct formula and still get the wrong result but with just a bit of knowledge, you can avoid VLOOKUP()'s gotchas.

Before we tackle the gotchas though, let's work through a short tutorial. This function has four arguments:

  • lookupvalue: This argument is required and specifies the value you're trying to match. If the value you supply is smaller than the smallest value in the lookup column, the function returns #N/A.
  • lookuptable: This argument is required and identifies the data you're trying to match and the columns that you'll be retrieving values from. The lookup column is always the left-most column in the table. You can reference the data range or use a range name.
  • offset: This argument is required and identifies the column, by positional offset to the lookup column, that you're retrieving values from. If you specify a value that's greater than the number of columns in lookuptable, the function returns #Ref!. If you specify a value less than 1, it returns #N/A.
  • match: This optional argument specifies whether the function finds an exact match or an approximate. The default is TRUE, which finds the closest approximate match if an exact match doesn't exist. When using this form, you must sort lookuptable by the lookup column or the formula may return the wrong result. FALSE forces an exact match so you don't have to sort lookuptable.

If you're lucky, an incorrect VLOOKUP() formula will return an error value because you'll know right away that's something's wrong. The #N/A error can be the result of several things, some less obvious than others:

  • Exact match not found: The lookup value doesn't exist in the left-most column. This can usually be resolved by sorting lookuptable or by using the appropriate match argument.
  • Mistmatched formats: Your lookup value and lookup column must have the same numeric format. The biggest culprit will be numeric values stored as text. A quick glance can help - remember, number characters stored as text are left aligned.
  • Dirty data: A simple character difference, such as a hyphen, a curly quote, a space character, a carriage return, or a nonbreaking space, can generate an error.

The #REF! error usually means you've referenced an offset column that's not in lookuptable.

I said earlier that you're lucky when you get an error, because you know something's wrong. Sometimes this function returns unexpected results and you don't realize there's a problem. Usually, these errors can be resolved by applying the match argument correctly and sorting lookuptable accordingly. Remember, the default, TRUE means you must sort the data first. This simple step, applied incorrectly, is probably responsible for most errors (not error values, but returning erroneous data). Here's a guideline that should help. If lookuptable isn't sorted, use FALSE. If you must use TRUE, be sure to sort lookuptable by the lookup column. Keep in mind that FALSE forces VLOOKUP() to check the entire column, so the larger the table, the slower it will perform - but most likely, you won't notice it.

One final word of caution: when copying a VLOOKUP() formula, be sure to state lookuptable as an absolute reference or a named range. If you fail to do so, Excel will update the table reference as it would any relative address, and the copied formulas will return errors.

About

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

8 comments
Dambo29
Dambo29

Hello,

All my columns are unformatted, and properly sorted in ascending order, the VLOOKUP formula looks at the correct cell yet provides the value for the cell above it.


For example, my table_array is as follows: Cell A1 = 0.1, A2 = 0.2, A3 = 0.3, A4 = 0.4, etc, for the second column, B1 = 0, B2 = 0, B3 = 1, B4 = 1, etc. Now I have the function lookup A3 but instead of giving me the value for B3, it gives the value for B2.


I have tried using the TRUE / FALSE statement at the end. When TRUE (or empty), it provides the wrong value (B2). When FALSE, it provides the #N/A error.


I did a test, where A3 instead of being 0.3 was now equal to 0.299 (and the formula was matching TRUE) and it provided the correct result. 


My lookup_value is generated by a simple formula (=X1-Y1-6) where X1 & Y1 are clean numbers of maximum 1 decimal.  (=13.0 - 6.7 - 6) which is exactly 0.3. So…


I did another test in which I just typed the value of 0.3 in the lookup_value cell and then it worked perfectly! The problem is, that it really needs to be a formula and not me typing in the values individually as they will all be different. So, is it a glitch with no workaround? 


Help would be GREATLY appreciated!

Marshwiggle
Marshwiggle

Since TRUE as the match argument is not only intended to find the closest approximate value, but the closest approximate value that is less than the lookup value, the lookup column must not only be sorted, but sorted in ascending order. Will sorting the lookup column in descending order find the closest match that is higher than the lookup value? Don't know. Haven't tried it, but I'll put it on my to-do list.

beckyco
beckyco

If you are looking values from a different sheet, the name of the sheet should be changed since the returns from "Sheet X" can give different results as well.

zimmerwoman
zimmerwoman

As I wrote last week, I have some bad juju in Microsoft. I get erroneous answers that are not errors sometimes if I do not sort the lookup table by the lookup value, even though I have used a "false" in the match column since I learned the function (12 to 15 years ago). Also, I use the iserr and some of the other information functions to return information to the list of values created by the lookup. So, if I have a numeric value and add an if statement that returns an error or na to the column, I can insert a zero and use that for sudden death calculations. I.e., if a certain characteristic is not present for that row/field combination, the formula inserts the 0; I use it in a calculation and the values for that row do not go forward included in calculations for the decision or calculation operation I am performing.

ssharkins
ssharkins

They're a good mix for more complex needs.

dogknees
dogknees

I've pretty much stopped using vlookup over the last few years and now use Match and Index instead. You get more control over the matching process and it's more versatile.

Editor's Picks