IFERROR with VLOOKUP in Excel

Publication Date :

Blog Author :

Download FREE IFERROR with VLOOKUP Excel Template and Follow Along!
IFERROR with VLOOKUP Excel Template.xlsx

Table Of Contents

arrow

IFERROR with VLOOKUP to Get Rid of #NA Errors

The IFERROR function is an error handling function, and the VLOOKUP function is a referencing function. These functions are combined and used so that when the VLOOKUP function encounters an error while finding or matching the data, the formula knows what to do. The VLOOKUP function is nested in the IFERROR function.

Examples

Example #1

Table 1 is the main data source. Table 2 is the VLOOKUP table. We have applied a  VLOOKUP  formula to find the sales amount for laptop brands in column F.

main data source

In the above table, we got an error for Apple and Notepad's brands. However, if we 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 the IFERROR function with the VLOOKUP function.

We must apply the IFERROR function before the VLOOKUP function in Excel. We need to write the Vlookup formula inside the IFERROR formula.

IFEEROR before VLOOKUP

=IFERROR (VLOOKUP (E3, $A: $B, 2, 0),"Data Not Found")

Firstly, the IFERROR function tries to find the value for the VLOOKUP formula.

Secondly, If the VLOOKUP function does not find a value, 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. We think this will look better than the #N/A.

IFERROR with LOOKUP 3

Example #2

Not only can we use the IFERROR function with the VLOOKUP function in Excel. We can use this with any other formula too.

Look at the below example where we need to calculate the variance percentage. If the base value is, the missing calculation returns the error as #DIV/0!

error as #DIV/0!

So, we can apply the IFERROR method here to eliminate ugly errors, #DIV/0!

#DIV/0 error

If any given calculation returns any error, the IFERROR returns the result as 0%. If there is no error, then the normal count will happen.

Manual Method to Replace #N/A or any other Error Types

However, we can replace errors with the IFERROR formula. There is  one manual method: the FIND and REPLACE method.

  • Step 1: Once the formula is applied, copy and paste only values.
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.

error type is #N/A
  • Step 3: Now, we must write the "Replace with" values as "Data Not Found."
Data Not Found
  • Step 4: Click on the "Replace All" button.
replace all button

It would instantly replace all the #N/A values with "Data Not Found."

replace all the #N/A values

Note: If we have applied a filter, we must choose the "Visible cells only" method to replace.

Things to Remember

  • The IFERROR function can make the numerical reports beautiful by removing errors.
  • If the data contains an error type and if we apply PivotTables, then the same kind of error will occur in the PivotTable too.
  • Though we can use the IFNA formula, it is not flexible to give results for errors other than #N/A.
  • In Excel 2007 and earlier versions, the formula to get rid of the #N/A error is an ISERROR.