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.
Related: Excel Formula Errors & How To Deal With Them
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 sortinglookuptable 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.
Also read
Why INDEX-MATCH Is Far Better Than VLOOKUP or HLOOKUP in Excel
How to Decide Which Excel Lookup Formula to Use