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.
IFERROR in VLOOKUP (Table of Contents)
- IFERROR with VLOOKUP in Excel
- IFERROR Function in Excel
- IFERROR with VLOOKUP Excel Normal Calculations
- Manual Method to Replace #N/A or any other Error Types
IFERROR with VLOOKUP to Remove #NA Errors in Excel
I am sure you must have come across #N/A value while working with VLOOKUP function. Firstly, we will discuss the reasons behind getting #N/A value in VLOOKUP function.
- Array range is not valid.
- Column index number is not valid.
- The lookup may not be there in the table array.
- Maybe lookup value has a spelling mistake or spacing problem. (This we can figure out using LEN, TRIM function)
- May you have not frozen the table array (that is the common mistake we all have done initially)
Then how do we figure out this #N/A values that omitted by VLOOKUP formula? I have experienced these kinds of problems at the start of my career.
Once my boss asked me to remove #N/A values from the sheet, which has 2000 rows of data. I was just out of moves.
Then researched and found a new way to do that task probably in less than a minute time. IFERROR is the formula that saved me tons of time and got an appreciation for doing it in less than a minute.
In this article, I will explain you the ways of using IFERROR with VLOOKUP in excel which can replace the error values with our own values.
IFERROR Function in Excel
The syntax of IFERROR function includes only two parameters and those are VALUE and VALUE if Error.
- VALUE: This is the required argument. This is the space for doing your calculation.
- Value if Error: If the first argument returns an error then what should be your result. The most common error types are #N/A, #REF! #DIV/0! #VALUE! #NUM! #NAME? and #NULL!.
IFERROR with VLOOKUP to Get Rid of #NA Errors
Consider the below IFERROR with VLOOKUP example for illustration of IFERROR with Vlookup in excel.
Table 1 is the main data source and table 2 is the Vlookup table. In column F, I have applied 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 VLOOKUP in Excel.
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 a 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 “Data Not Found” text. I think this will look better than the #N/A.
IFERROR with VLOOKUP Excel Normal Calculations
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, 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 the 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 error with 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 in IFERROR with VLOOKUP in Excel
- 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 Pivot table too.
- Though we can use 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 –
- Null in Excel
- HLOOKUP Examples in Excel
- Explanation of VLOOKUP with SUM
- Statement of VLookup with IF Function in Excel
- VLookup in VBA Excel
- Lookup Function with Excel Alternatives to VlooKup
- Excel Delete Pivot Table
- Excel VLOOKUP Error
- Refresh Pivot Table in Excel
- Data Filters in Excel
- HLOOKUP Formula in Excel
- OFFSET Excel
- COMBIN Excel
- TRANSPOSE in Excel
- 35+ Courses
- 120+ Hours
- Full Lifetime Access
- Certificate of Completion
