IFERROR with VLOOKUP to Get Rid of #NA Errors
As we know IFERROR is an error handling function and Vlookup is a referencing function, these functions are combined and used so that when Vlookup encounters an error while finding or matching the data the formula must know what to do when it encountered an error, Vlookup function is nested in the iferror function.
Examples
Example #1
Table 1 is the main data source and table 2 is the Vlookup table. In column F, I have applied a Vlookup formula to find the sales amount for laptop brands.
In the above table, I got an error for the brands Apple and Notepad. If you look at the main data table, there are no Apple and Notepad brands. That is why Vlookup has returned an error type as #N/A.
We can fix this issue by using IFERROR with the VLOOKUP function.
Apply IFEEROR before VLOOKUP in excel. We need to write the Vlookup formula inside the IFERROR formula.
=IFERROR (VLOOKUP (E3, $A: $B, 2, 0),”Data Not Found”)
Firstly, IFERROR trying to find the value for the VLOOKUP formula.
Secondly, If VLOOKUP does not find value then it will return an error. Therefore, if there is an error we will show the result as “Data Not Found”.
We have replaced all the #N/A values with the “Data Not Found” text. I think this will look better than the #N/A.
4.9 (1,353 ratings) 35+ Courses | 120+ Hours | Full Lifetime Access | Certificate of Completion
Example #2
Not only can we use IFERROR with VLOOKUP in Excel. We can use this with any other formula too.
Look at the below example where I need to calculate the variance percentage. If the base value is, the missing calculation returns the error as #DIV/0!
So we can apply the IFERROR method here to get rid of ugly errors i.e. #DIV/0!
If any given calculation returns any kind of error then IFERROR returns the result as 0%. If there is no error then the normal calculation will happen.
Manual Method to Replace #N/A or any other Error Types
However, we can replace errors with the IFERROR formula there one manual method to do it and that is found and replace method.
- Step 1: Once the formula is applied and copy and paste only values.
Step 2: Press Ctrl + H to open replace the box and type #N/A If the error type is #N/A.
- Step 3: Now write the replace with values as “Data Not Found”.
- Step 4: Click on replace all button.
This would instantly replace all the #N/A values with Data Not Found.
Note: If you have applied filter please choose visible cells only method to replace.
Things to Remember
- IFERROR can make your numerical reports beautiful by removing all kinds of errors.
- If the data contains error type and if you apply pivot tables then the same kind of error will occur in the Pivot table too.
- Though we can use the IFNA formula, it is not flexible enough to give results for the errors other than #N/A.
- In Excel 2007 and earlier version, the formula to get rid of #N/A error is ISERROR.
Recommended Articles
This has been a guide to on how to use IFERROR with VLOOKUP in Excel to remove #NA Errors values. Here we take practical excel examples along with downloadable excel templates. You may also look at these useful functions in excel –
- 35+ Courses
- 120+ Hours
- Full Lifetime Access
- Certificate of Completion
