Excel Productivity Guide
Useful Excel Tips | Keyboard Shortcuts | VBA Codes
Download this Free E-Book to take your excel skills to a whole next level using some smart Excel tactics.
Worth $20, Absolutely Free
While using VLOOKUP, whenever the value for which you are looking for is not there in the data you get #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 name and you want to lookup for the name “John”. But unfortunately, that name is not there in the list.
Now in this situation, VLOOKUP will return #N/A. You know the reason of this error but maybe not everyone knows. So, the better way is to replace #N/A with a custom value so that everyone can understand why this error is there.
And for replacing #N/A error, the best way is to use IFERROR with VLOOKUP.
IFERROR is a function which 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 #N/A error by combining it with IFERROR. But before you combine both of these functions you need to understand how IFERROR works.
In 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:
First of all, IFERROR evaluates the value.
After that, if the value is an error it returns the value you have specified for the error, otherwise the value itself.
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 value_if_error argument. And, in all the cell where you have an error, it has returned “Not Found”.
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 for 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 gets the same result which VLOOKUP has returns.
You need to understand one thing that IFERROR replace the value for all types of errors. So, you will get 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 evaluate #N/A.
In older version of Excel, IFERROR is not there. But you can still replace #N/A with a custom value. For this, you need to combine IF and ISNA with VLOOKUP.
And, the formula will be:
In this formula, you have evaluated VLOOKUP with ISNA (which only evaluates #N/A and return 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 which is the same VLOOKUP formula.
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 #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.