IFERROR


Explanation of the IFERROR function

The IFERROR function has 2 arguments as shown below:

  1. value
  2. value_if_error

You can use other Excel functions within both of the arguments as well.

Note: I typically use the IFERROR to clean up the results of my formulas if they return an ERROR. I do this by setting the value_if_error to a blank value with 2 quotations side by side “” but your value_if_error can be anything you choose.

IFERROR syntax

1. value

This is the value that the IFERROR function will use to determine if the value is an ERROR:

  • If the value is an error, it will return the result of the value_if_error
  • If the value is not an error, it will return the result of the value

2. value_if_error

This is what you want Excel to do if your value is an error. Some examples include:

  • Return a blank value by using 2 quotations side by side “”
  • Write a vlookup to bring a value from somewhere
  • Use any Excel function or combination of functions to evaluate for a result
  • Perform a mathematical calculation
  • Return a fixed result (number or text)

Examples of when to use an IFERROR function

  • VLOOKUP function that doesn’t find your lookup value and returns an #N/A error
  • Dividing by 0 (zero) and returns a #DIV/0! error

I will cover the 2 examples above, but you can use the IFERROR function for any other errors or scenarios you are dealing with.

IFERROR with VLOOKUP

In the example below I am looking for 2 names (1 which exists in my data and 1 that does not) and forcing the result of the error to “Not Found” when the VLOOKUP function returns an error.

Note: I used an absolute reference (using $ sign) for my lookup_range in the VLOOKUP formula below.

IFERROR example_1

IFERROR with mathematical calculations (divide by zero)

In the example below I am forcing the error to show as a blank value, or else it will return the result of the division (if no error).

IFERROR example_2

 

Go back to Excel Tutorials.