Excel’s IFERROR function traps errors that could be returned from a formula or calculation, and gives you control over what is returned instead of an error message.
For example, instead of a formula returning Excel’s default ‘#N/A’ error, you might want to:
- return specific text,
- no text at all, or
- perform a different function or formula.
IFERROR checks for the following errors:
#N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, #NULL!.
If the function/formula/calculation returns a valid answer, its result is returned as normal.
Syntax – Excel definition
=IFERROR( value, value_if_error )
Syntax – plain English definition
=IFERROR( Formula/calculation to be checked for errors [result will be returned if no error], Value/formula/calculation to be returned if there’s an error )
Syntax – example – IFERROR then blank
=IFERROR( A2/B2 , “” )
The example above calculates the formula ‘A2 divided by B2’, and if this results in an error, the result returned is a blank cell. If A2/B2 produces a valid result it is returned as normal.
Syntax – example – IFERROR and VLOOKUP
=IFERROR( VLOOKUP ( G4,$B$2:$D$26,3,FALSE ) , “Value not found” )
The above example performs a lookup for a value (in cell G4) in a range ($B$2:$D$26), and if the value is not found, then the string “Value not found” is returned.
Syntax – example – IFERROR then perform another function
=IFERROR( I8/J8 , I8/$M$4 )
In the example above, the calculation in row 8, I8/J8 (55 divided by 0) results in an error, so the second formula (I8/$M$4) is calculated instead.