IFERROR with VLOOKUP in Excel

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

You can download this IFERROR with VLOOKUP Excel Template here – IFERROR with VLOOKUP Excel Template

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.

main data source

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.

IFEEROR before VLOOKUP

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

Firstly, IFERROR is 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.

IFERROR with LOOKUP 3

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!

error as #DIV/0!

So we can apply the IFERROR method here to get rid of ugly errors, i.e., #DIV/0!

#DIV/0 error

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.

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 write the replace with values as “Data Not Found.”

Data Not Found

  • Step 4: Click on replace all button.

replace all button

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

replace all the #N/A values

Note: If you have applied filter, please choose the visible cells only method to replace.

Things to Remember

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
LEARN MORE >>