Use IFERROR with VLOOKUP to Get Rid of #N/A Errors (2024)

• Sumit Bansal

When using the VLOOKUP formula in Excel, sometimes you may end up with the ugly #N/A error. This happens when your formula can not find the lookup value.

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:

1. The lookup value is not found in the lookup array.
2. There is a leading, trailing, or double space in the lookup value (or in the table array).
3. There is a spelling error in the lookup value or the values in the lookup array.

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 errorscaused 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.

=IFERROR(VLOOKUP(D2,$A$2:$B$10,2,0),"Not Found")

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:

=IFERROR(VLOOKUP(G3,$A$2:$B$5,2,0),IFERROR(VLOOKUP(G3,$D$2:$E$5,2,0),"Not Found"))

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, andreturning 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!
• Using VLOOKUP From Another Sheet in VBA