Remove Vlookup #N/A Error in Excel

Add to Favorites

How to remove the #N/A error from Vlookup and replace it with a friendly message or a blank cell.

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:

=IFERROR(VLOOKUP(A2,D3:E5,2,FALSE),"")

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

=IFERROR(VLOOKUP(A2,D3:E5,2,FALSE),"No Color")

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:

=IF(ISERROR(VLOOKUP(A2,D3:E5,2,FALSE)),"No Color",VLOOKUP(A2,D3:E5,2,FALSE))

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.


Excel Function: IFERROR(), ISERROR(), VLOOKUP()
Downloadable Files: Excel File

Similar Content on TeachExcel
Update, Change, and Manage the Data Used in a Chart in Excel
Tutorial: In this tutorial I am going to show you how to update, change and manage the data used by ...
Remove All Filtering From a Worksheet in Excel
Macro: This Excel macro removes all filtering from a worksheet in Excel. This allows you to compl...
Remove Comments from a Workbook in Excel - All Comments Deleted - Macro VBA
Macro: Excel macro that will remove/delete all of the comments from an entire Excel workbook....
Remove All Data Validation from a Cell in Excel
Macro: Remove all data validation from a cell in Excel with this free Excel macro. This is a grea...
Remove Gridlines from Only Part of a Worksheet in Excel
Tutorial: How to remove gridlines from a part of a worksheet in Excel instead of the entire workshe...
Delete All Empty Rows or Blank Cells from a Range in Excel
Tutorial: How to quickly delete all empty cells or rows from a range in Excel.  This allows you to q...