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*:*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.

Also read

**Why INDEX-MATCH Is Far Better Than VLOOKUP or HLOOKUP in Excel
How to Decide Which Excel Lookup Formula to Use**