Exceljet

Quick, clean, and to the point

What to do when VLOOKUP returns NA

When you start using VLOOKUP, you'll often run into situations where VLOOKUP can't find a match and displays an error.

In this video, we'll look how to avoid and handle that situation.

Here we have a VLOOKUP example we've looked at previously, which uses VLOOKUP to retrieve employee information based on an id.

When you're using VLOOKUP this way you'll probably run into situations where VLOOKUP can't find the value you're trying to look up.

The first thing to check is your lookup table. Make sure the table is properly defined and the lookup value is in the left most column.

Most often when VLOOKUP can't find something in the table, it's because the value doesn't exist in the lookup table. For example, if I type the id E999, which doesnt' exist in the employee table, we'll see NA errors for all VLOOKUP formulas. That's because there is no employee with id E999.

Sometimes though, the problem is more subtle. For example, the code E785 is valid, and VLOOKUP correctly retrieves the information for Harold.

However, if I add a space to to the end of the code, the lookup will fail with an NA error

Likewise, if I add a space after the code as it appears in the table, the lookup will also fail.

In both cases, it looks like VLOOKUP should work, but it doesn't due to a typo that's hard so see. So, take care that your data and lookup values are clean and don't contain extra characters.

Often though, a lookup fails because a value really doesn't exist in the lookup table. In that case, you may want to display an error message that's more friendly than N/A

One easy way to do this is to wrap the VLOOKUP function inside a function called IFERROR. You can use IFERROR to trap erros and return a more friendly value.

Simply add VLOOKUP as the first argument of the function and for the second argument, add the message you'd like to display when VLOOKUP returns NA. In this case, I'll use "Not found".

Now when a value can't be found, VLOOKUP will throw the NA error, and IFERROR will catch that error and display "Not found".

Be careful if you use this technique, because IFERROR will catch any error that is thrown by VLOOKUP. For example, if I misspell VLOOKUP function Excel throws a #NAME error, but all you see on the worksheet is the Not Found message, which is misleading.

Course 

Related shortcuts

CtrlZ
Z
Author 
Dave Bruns