In this tutorial, I will show you different ways to use IFERROR with VLOOKUP to handle these #N/A errors cropping up in your worksheet. Using the combination of IFERROR with VLOOKUP allows you to show something meaningful in place of the #N/A error (or any other error for that matter). Before getting into details on using this combination, let’s first quickly go through the IFERROR function and see how it works.

IFERROR Function Explained

With IFERROR function, you can specify what should happen in case a formula or a cell reference returns an error. Here is the syntax of the IFERROR function. =IFERROR(value, value_if_error)

value – this is the argument that is checked for the error. In most cases, it is either a formula or a cell reference. When using VLOOKUP with IFERROR, the VLOOKUP formula would be this argument. value_if_error – this is the value that is returned if there is an error. The following error types evaluated: #N/A, #REF!, #DIV/0!, #VALUE!, #NUM!, #NAME?, and #NULL!.

Possible Causes Of VLOOKUP Returning a #N/A Error

VLOOKUP function may return a #N/A error due to any of the following reasons: You can handle all these causes of error with the combination of IFERROR and VLOOKUP. However, you should keep an eye out for cause #2 and #3, and correct these in the source data instead of letting IFERROR handle these. Note: IFERROR would treat an error irrespective of what caused it. If you only want to treat the errors caused by VLOOKUP not being able to find the lookup value, use IFNA instead. That will make sure that errors other than #N/A are not treated and you can investigate these other errors. You can treat leading, trailing, and double spaces using the TRIM function.

Replacing VLOOKUP #N/A Error with Meaningful Text

Suppose you have a dataset as shown below:

As you can see that the VLOOKUP formula returns an error as the lookup value is not in the list. We are looking to get the score for Glen, which is not in the table of scores. While this is a very small dataset, you may get huge datasets where you have to check the occurrence of many items. For every case when the value is not found, you will get a #N/A error. Here is the formula you can use to get something meaningful instead of the #N/A error.

The above formula returns the text “Not Found” instead of the #N/A error. You can also use the same formula to return blank, zero, or any other meaningful text.

Nesting VLOOKUP With IFERROR Function

In case you are using VLOOKUP and your lookup table is fragmented on the same worksheet or different worksheets, you need to check the VLOOKUP value through all of these tables. For example, in the dataset shown below, there are two separate tables of student names and the scores.

If I have to find the score of Grace in this dataset, I need to use the VLOOKUP function to check the first table, and if the value is not found in it, then check the second table. Here is the nested IFERROR formula I can use to look for the value:

Using VLOOKUP with IF and ISERROR (Versions prior to Excel 2007)

IFERROR function was introduced in Excel 2007 for Windows and Excel 2016 in Mac. If you’re using the prior versions, then IFERROR function will not work in your system. You can replicate the functionality of IFERROR function by using the combination of the IF function and the ISERROR function. Let me quickly show you how to use the combination of IF and ISERROR instead of IFERROR.

In the above example, instead of using IFERROR, you can also use the formula shown in cell B3: =IF(ISERROR(A3),”Not Found”,A3) The ISERROR part of the formula checks for the errors (including the #N/A error) and returns TRUE if an error is found and FALSE if not.

If it’s TRUE (which means that there is an error), the IF function returns the specified value (“Not Found” in this case). If it’s FALSE (which means that there is no error), the IF function returns that value (A3 in the above example).

IFERROR Vs IFNA

IFERROR treats all kinds of errors while IFNA treats only the #N/A error. When handling errors caused by VLOOKUP, you need to make sure you’re using the right formula. Use IFERROR when you want to treat all kinds of errors. Now an error can be caused by various factors (such as the wrong formula, misspelled named range, not finding the lookup value, and returning error value from the lookup table). It wouldn’t matter to IFERROR and it would replace all these errors with the specified value. Use IFNA when you want to treat only #N/A errors, which are more likely to be caused by VLOOKUP formula not being able to find the lookup value. You May Also Find the Following Excel Tutorials Useful:

How to make VLOOKUP Case Sensitive. VLOOKUP Vs. INDEX/MATCH – The Debate Ends Here! Use VLookup to Get the Last Number in a List in Excel. How to Use VLOOKUP with Multiple Criteria #NAME Error in Excel – What Causes it and How to Fix it!

I received “#N/A” when both the source cell and target cell has no values. Can someone help me here please? thanks in advance. As far as I know, IFNA is not available in 2007 and 2010 versions of Excel Windows. Instead of going the longer IF + ISNA, route, it’s easier to check the formula and make sure there are no errors in the formula or named range and go with IFERROR instead. Another example is of a recent dashboard i worked on, where the data I got itself had errors such as DIV and NA. Instead of going a 2-step process of checking with IFNA and then treating the DIV errors with IFERROR, it’s better to make sure the formula/named range is correct and then use IFERROR. Also, wrong range anyway returns NA error, so even IFNA wouldn’t help in that case. My point is, IFNA is better, but it’s not the only way to go. You may also be getting an error if the range looked up is too short narrow, or if the cell value returned (legitimately) is itself an error, or if the index is negative, or if the lookup range is unsorted and the last element in the VLOOKUP is omitted, or if the lookup value is an error, or maybe mistyped a range name or … What I’m getting at is that errors can occur for many reasons and defaulting the error response to “not found” may mean you’re going to overlook an incorrect formula Much better to anticipate the error (with a COUNTIF in this case) and deal with it properly IFERROR is a very dangerous thing to use in such a cavalier manner – beware jim Also, the next step would always be to make sure the formula is created properly. There wouldn’t be any other way to handle misspelled named range or not having the proper data structure or not having the right lookup range, than to make sure it’s sorted in the first place. In case of approximate match, having the data sorted in an ascending order is a pre-requisite to use the VLOOKUP formula. That would anyway lead to wrong results (even in cases when the result is not an error). In case the formula returns a result that is an error, IFERROR would still be valuable in making the result more meaningful. The cases where this combination works well is when you get data download from a data set that have fixed formats and you need to perform lookup on such data. When used properly, IFERROR can be a really useful function.