Every time you use Excel’s VLOOKUP formula, there may be times when you end up with the unsightly #N/A error.
That is what happens whenever your formula cannot search for the lookup value.
Our tutorial will provide you with the various ways anyone can use VLOOKUP with IFERROR so you can effectively handle any #N/A errors that could crop up in your Excel worksheet.
Combine the use of VLOOKUP and IFERROR.
It will allow you to create something more meaningful compared to an error like the #N/A error.
Before we can get into the details on how to use this combination, we need first to discuss what the IFERROR function is and how it works.
An Explanation of the IFERROR Function
The IFERROR function allows you to specify what could happen whenever a cell reference or formula gives an error.
Here is the IFERROR function’s syntax:
- value_if_error – whenever an error occurs, this is the value that gets returned. These are the error types that are evaluated: #REF!, #N/A, #VALUE!, #DIV/0!, #NAME?, #NULL!, #NUM!.
- Value – the argument that gets checked for the error is value. Most of the time, it can either be a cell reference or a formula. Whenever you use IFERROR with VLOOKUP, the formula in VLOOKUP will become this argument.
Probable Reasons Why VLOOKUP Returns the #N/A Error
The #N/A error may what be the VLOOKUP function could return because of these reasons:
- The lookup array’s values or the lookup value has a spelling error.
- You cannot find the lookup value in the lookup array.
- The table array or lookup value has double space, trailing, or leading.
All of these error causes can be handled whenever you combine them with VLOOKUP and IFERROR.
Yet, you need to review if they are either cause #1 and #3 as these are in the source data that IFERROR may not be able to handle.
Note: Whatever may have caused it, IFERROR will still treat it as an error. If you only want to fix the mistakes made by VLOOKUP not finding the lookup value, have IFNA used instead. Doing this can ensure that the errors aside from #N/A don’t get treated to have these other errors investigated. Use the TRIM function if you want to treat trailing, leading, as well as double spaces.
Having the IFERROR Function Nested with VLOOKUP
Whenever you use VLOOKUP, you can have a lookup table that looks fragmented on different worksheets or the same worksheet.
With this, you have to go through every table to check the VLOOKUP value.
Here is a dataset wherein there are separate tables of students’ names, including their scores.
*insert table Nesting VLOOKUP With IFERROR Function
Whenever you need to look for the score of Martha in this worksheet, you have to use the function VLOOKUP so you can review the first table.
Whenever you can’t see the value there, have the second table checked.
Nest the IFERROR formula and use it to search for the value:
Use of Meaningful Text to Replace the VLOOKUP #N/A Error
Assume that you have the following dataset:
You can see that the formula VLOOKUP returns an error since the lookup value is not included in the list.
We are searching for Glen’s score that is not included in the table of scores.
Even if this dataset is relatively small, you may experience massive datasets where you need to check if so many items occur.
Whenever you don’t get the value, what you will get is the error #N/A.
You can have this formula used not just to get the #N/A error but attain something meaningful.
This formula will return the text “Not Found” and not give the error #N/A.
You can also use the same formula to return zero, blank, or any meaningful text.
IFNA VS IFERROR
IFERROR works on treating every kind of error while IFNA only treats the error #N/A.
It is essential to have the correct formula used whenever you handle errors that VLOOKUP caused.
Allow the use of IFNA whenever the #N/A errors are the only ones you want to treat.
These are likely because the VLOOKUP formula is unable to search for the lookup value.
Use the IFERROR whenever you want every kind of error treated.
An error may be due to numerous factors that include returning the lookup table’s error value, inability to search for the lookup value, name range that is misspelled, and incorrect formula.
These would not matter to IFERROR, and it will have the specified value to replace all of the errors.
Use of VLOOKUP with ISERROR and IF in Versions older than Excel 2007
The function IFERROR got introduced for Windows Excel last 2007 and Mac Excel in 2016.
If you are using the old versions, the function IFERROR may not work in your system.
Make the functionality of the IFERROR function replicated.
Do this with the use of the combination of the ISERROR function and the IF function.
Let us make you see how you can combine ISERROR and IF used instead of the IFERROR.
In the example above, instead of having IFERROR used, you can have this formula used in cell B3:
=IF(ISERROR(A3),” Not Found”,A3)
With ISERROR as a part of the formula, it will check for errors that include the #N/A error. It will return FALSE if it doesn’t find a mistake and TRUE if it finds an error.
- When you get FALSE, it means that there’s no error. The IF function will return the value. In our example, it is A3.
- In case you get TRUE, it means that there’s an error. The IF function will return the specified value, which is “Not Found” in our example.
Allow Yourself to Know About IfError VLookUP with ExcelMaster
Become highly proficient in Excel with us! Make yourself well-informed about IfError VLookUP with ExcelMaster NOW!