IFERROR with VLOOKUP in Excel to Replace #N/A in Excel (Formula)

While using VLOOKUP, whenever the value for which you are looking is not there in the data you get a #N/A error. In simple words, whenever VLOOKUP is not able to find the value it returns #N/A.

For example, you have a list of employees’ names and you want to lookup up the name “John”. But unfortunately, that name is not there on the list.

Now in this situation, VLOOKUP will return #N/A. You know the reason for this error but maybe not everyone knows. So, the better way is to replace #N/A with a customs value so that everyone can understand why this error is there.

And for replacing the #N/A error, the best way is to use IFERROR with VLOOKUP. IFERROR is a function that can help you to evaluate a value and if that value is an error it will return a custom value instead of that error.

So, today in this post, you will learn how to use VLOOKUP without a #N/A error by combining it with IFERROR. But before you combine both of these functions you need to understand how IFERROR works.

How IFERROR Works

In the IFERROR function, you need to specify two things. First, the value you want to evaluate, and second, a custom value you want whenever an error occurs. Here is the syntax for IFERROR:

IFERROR(value, value_if_error)

• value: A Value to evaluate.
• value_if_error: Value which you want to get in return when an error occurs.

1. First of all, IFERROR evaluates the value.
2. After that, if the value is an error it returns the value you have specified for the error, otherwise the value itself.

Combine IFERROR with VLOOKUP

Now, it’s time to combine VLOOKUP and IFERROR. For this, in IFERROR you need to simply replace the value with the VLOOKUP.

And, for the value_if_error argument, specify a value you want to return whenever VLOOKUP returns #N/A.

Here in the below example, you have used “Not Found” for the value_if_error argument. In all the cells where you have an error, it has returned “Not Found”.

How Both Functions Work

In this combination, both of the functions work in a sequence. First, VLOOKUP works in the same manner in which it works all the time. It lookup up a value and returns the result value if found, otherwise, an error.

Second, IFERROR evaluates the result of VLOOKUP. And, if that result is an error it will replace that error with the custom value you have specified. Otherwise, it will do nothing and you get the same result that VLOOKUP has returned.

Point To Remember

You need to understand one thing IFERROR replaces the value for all types of errors. So, you will get the same custom value for all the errors.

And, if there is any chance for you to get any error other than #N/A then you can replace IFERROR with IFNA which only evaluates #N/A.

Problem with Older Version

In the older version of Excel, IFERROR is not there. But you can still replace #N/A with a customs value. For this, you need to combine IF and ISNA with VLOOKUP. And, the formula will be:

=IF(ISNA(VLOOKUP(A1,table,2,FALSE)),"Not Found",VLOOKUP(A1,table,2,FALSE))

In this formula, you have evaluated VLOOKUP with ISNA (which only evaluates #N/A and returns TRUE). So when VLOOKUP returns an error IFNA converts it into TRUE.

And after that, IF returns the value which you have specified for TRUE. Otherwise, the value for FALSE is the same VLOOKUP formula.

Conclusion

I am a strong believer that you should always use IFERROR with VLOOKUP to replace #N/A. The biggest reason is when you have numeric values in your data, then it’s not possible to use values with an error.

For example, if you have a pivot table and in the source data you have a #N/A error, then the same error will reflect in the pivot table. So the best way is to replace that error with a “0”.

I hope you found this formula tip useful. And, it would be great if you share this tip with your friends.