|
Classic file types are no longer available for use as of January 2021. You can transition your classic files or download a PDF. Learn More

Workiva Support Center

IFERROR

IFERROR

  • Last updated on

Description

Use this function to trap and handle errors in a formula.

 

Syntax

IFERROR(value, value_if_error)

 

Inputs

This function accepts the following arguments:

Name Required Description Valid input
value Yes The value or expression to check for an error. A cell reference, a cell range, a number, a text string, or a formula which results in any of these.
value_if_error Yes The value to return if the formula evaluates to an error. A cell reference, a cell range, a number, a text string, or a formula which results in any of these.

 

Examples

Sample data

  A B
1 100 20
2 50 0
3 30 5
4 Four 10

Sample formulas

Use case Formula Explanation and Result
Divide the value in one cell by the value in another. If there's an error, it returns the supplied message. =IFERROR(A1/B1,"Cannot divide")

This formula works in the following manner:

  • A1/B1: Attempts to divide the value in A1 (100) by the value in B1 (20).
  • IFERROR(...): The calculation 100/20 results in 5, which is not an error. Therefore, the function returns the result of the calculation.

For this data set this formula returns 5.
Divide the value in one cell by the value in another. If there's an error, it returns the supplied message. =IFERROR(A2/B2,"Cannot divide")

This formula works in the following manner:

  • A2/B2: Attempts to divide the value in A2 (50) by the value in B2 (0). This operation results in a #DIV/0! error.
  • IFERROR(...): Catches the #DIV/0! error and returns the specified value_if_error, which is "Cannot divide".

For this data set this formula returns Cannot divide.
Multiply the value in one cell by the value in another. If there's an error, it returns the supplied message. =IFERROR(A4*B4,"Invalid calculation")

This formula works in the following manner:

  • A4*B4: Attempts to multiply the value in A4 ("Four") by the value in B4 (10). This operation results in a #VALUE! error.
  • IFERROR(...): Catches the #VALUE! error and returns the specified value_if_error, which is "Invalid calculation".

For this data set this formula returns Invalid calculation.
Look for a specific value in a range. If there's an error, it returns the supplied message. =IFERROR(VLOOKUP("Banana",A1:B4,2,FALSE),"No Banana found")

This formula works in the following manner:

  • ("Banana",A1:B4,2,FALSE): Attempts to find the exact text "Banana" in the first column of the range A1:B4. Since "Banana" is not present in A1:A4, the VLOOKUP function fails and returns an #N/A error.
  • IFERROR(...): Catches the #N/A error and returns the specified value_if_error, which is "No Banana found".

For this data set this formula returns No Banana found.

 

Notes

  • IFERROR returns a specified value if a formula evaluates to an error; otherwise, it returns the result of the formula.
  • IFERROR catches all error classes (#N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, and #NULL!). It doesn't distinguish between error classes.
  • IFERROR is often used with functions such as VLOOKUP, HLOOKUP, and INDEX/MATCH to handle missing data scenarios.
  • IFERROR can be nested within other functions or used as part of a larger formula.

 

Tips

  • Use IFERROR to clean up spreadsheets by replacing error messages with more user-friendly text or blank cells.
  • Be cautious when using IFERROR, as it can mask legitimate errors that you might want to investigate.
  • Consider using the more specific IFNA function if you only need to catch #N/A errors.
  • For more complex error handling, you can nest IF statements within IFERROR.
  • When troubleshooting, temporarily remove IFERROR to see the actual errors your formulas are producing.

 

Related functions

AND
CHOOSE
IF
IFERROR
IFNA
IFS
NOT
OR

Was this article helpful?
1 out of 2 found this helpful
Description Use this function to trap and handle errors in a formula.   Syntax IFERROR(value, value_if_error)   Input...