IFERROR with VLOOKUP to Get Rid of #NA Errors

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.

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 formulaWrite The Vlookup FormulaThe VLOOKUP excel function searches for a particular value and returns a corresponding match based on a unique identifier. A unique identifier is uniquely associated with all the records of the database. For instance, employee ID, student roll number, customer contact number, seller email address, etc., are unique identifiers. read more inside the IFERROR formula.

=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.

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!

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

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.

Step 2: Press “Ctrl + H” to open, replace the box, and type “#N/A” If the error type is #N/A.

  • Step 3: Now, we must write the “Replace with” values as “Data Not Found.”

  • Step 4: Click on the “Replace All” button.

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

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 ISERRORISERRORISERROR is a logical function that determines whether or not the cells being referred to have an error. If an error is found, it returns TRUE; if no errors are found, it returns FALSE.read more.

This article has been a guide on 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: –

  • VBA IIFVLOOKUP ErrorsError Bars in ExcelExcel VBA IFERROR