In a Nutshell
The IFERROR function is a powerful tool that can be used to trap and handle errors in Excel formulas. It can be used to replace error values with text, numbers, or other formulas.
Syntax
The syntax for the IFERROR function is as follows:
=IFERROR(value, value_if_error) - value: The value, reference, or formula to check for an error.
- value_if_error: The value to return if an error is found.
The IFERROR function can be used to trap and handle the following error types:
- #N/A: The value is not available.
- #VALUE!: The value is invalid.
- #REF!: The cell reference is invalid.
- #DIV/0!: The formula is trying to divide by zero.
- #NUM!: The value is outside the range of valid numbers.
- #NAME?: The formula is referencing a name that is not defined.
- #NULL!: The cell is empty.
Examples
Example 1
For example, if the value in cell B1 is zero, the following formula will return the text “Error!”, because the formula A1/B1 will be dividing by zero, giving us a #DIV/0! error:
=IFERROR(A1/B1, "Error!")Example 2
This formula will return the value in the second column of the range B:C that matches the value in cell A1. But if no match is found, the formula will return the text “Not found.”
=IFERROR(VLOOKUP(A1, B:C, 2, FALSE), "Not found")The IFERROR function in Example 2 above will capture any error, so if a match is found in the vlookup, but that match is an error that we need to know about (e.g. #DIV/0!), then it will incorrectly say “Not found” when it was actually found. We can make the formula more specific by using a different function, namely, IFNA. If a value is not found in the VLOOKUP then it brings back an #N/A! error, so we should only say “Not found” when that error pops up, not #REF!, #DIV/0!, etc. So let’s change the formula above to this one:
=IFNA(VLOOKUP(A1, B:C, 2, FALSE), "Not found")
The IFERROR function can be used to replace error values with text, numbers, or other formulas. This can be useful for making your spreadsheets more user-friendly and for preventing errors from affecting your calculations.
- To replace error values with text:
=IFERROR(A1, "This cell is empty")
- To replace error values with numbers:
=IFERROR(A1, 0)
- To replace error values with other formulas:
=IFERROR(A1, B1 + C1)
