Excel IFERROR function — examples: return a blank; with VLOOKUP; perform a different function

Image of software function sign fx

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.