Remove Vlookup #N/A Error in Excel

Fix the Vlookup Error in Excel 2007, 2010, 2013, and Later

Fix the Vlookup Error in Excel 2003

The Problem

You get the #N/A error when Vlookup can't find any data or when you haven't entered a value for the lookup_value argument.

The Vlookup function is in cell B2 but illustrated in cell A4 so you can see it.

The problem is that nothing has been entered into cell A2 for the color, so we get an error in cell B2 that is not at all helpful.

Fix the Vlookup Error in Excel 2007, 2010, 2013, and Later

To remove the error, we can use the IFERROR() function, which was introduced in Excel 2007.

For the first argument, we use our original Vlookup function.

Then we type a comma and put what we want to appear if the Vlookup function would return an error.

This is the result:

Note that you need to put your output in quotation marks if you want to return text or a blank space, like I did above.

I could also make a nice text response here:

Here is the code for the first example that returns a blank:

Here is the code for the second example that returns text:

When we input a color, the Vlookup works like it should:

Fix the Vlookup Error in Excel 2003

If you are using Excel 2003, you will need to use two functions to get this to work (there is no IFERROR() function in Excel 2003).

We need to use the IF() function and the ISERROR() function this time.

First, the IF() function:

Then the ISERROR() function:

Then our Vlookup function:

Then, close the ISERROR function with a parenthesis, type a comma to go to the next argument, and input the value that will appear if the Vlookup function causes an error:

Then type a comma to go to the last argument for the IF statement and input the Vlookup function once again and add a closing parenthesis for the IF statement:

Hit Enter and you are done!

WOW, that was a lot more work!

Here is the full formula that we just entered:

It seems complex but that's just because we need more functions and we have to put the Vlookup function in there twice.

You can replace No Color with whatever text you want or just remove the text but keep the quotes to have it show a blank cell.

Notes

Make sure to download the sample spreadsheet so you can see both of these methods in action.

If you use Excel 2007 or later, definitely use the IFERROR method!!!

Remember that with the Excel 2003 method both Vlookups must be exactly the same.