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

Result: 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".

Result: 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".

Result: 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".

Result: 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

DescriptionUse this function to trap and handle errors in a formula. SyntaxIFERROR(value, value_if_error) InputsThis ...