Image of software function sign fx

Excel’s IFERROR function traps errors that are returned from a formula or calculation. It gives you control over what is returned instead of an error message, such as IFERROR then blank.

Showing Excel IFFERROR and VLOOKUP with custom text returned

For example, instead of a formula returning Excel’s default ‘#N/A’ error, you might want to:

IFERROR() checks for the following errors:

#N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, #NULL!.

A valid answer will be returned as normal if there is no error.

IFERROR Excel definition

=IFERROR( result [you enter your formula/function here], value_if_error )

IFERROR() arguments
  • result = value resulting from a function / formula / calculation; you enter your formula/function here to be checked in the IFERROR function;
  • value_if_error = the value or instruction you specify for Excel to return if ‘result‘ is an error;
  • If the function / formula / calculation returns a valid answer, its result is returned as normal.
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 is an error )

IFERROR then blank – syntax

=IFERROR( A2/B2 , “” )

The example above calculates the formula ‘A2 divided by B2’ (i.e. cell contents of A2 and 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.

IFERROR and VLOOKUP – syntax

=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) in column 3, and if the value is not found, then the string “Value not found” is returned.

The VLOOKUP function is wrapped inside an IFERROR() function.

Excel IFERROR and SEARCH with custom text returned.
Another example showing that the word ‘mini’ is being searched for in the Item column, and if not found, the text “Not found” is returned.

See our post here on how to use the SEARCH function with IFERROR https://excelquick.com/excel-function/search-specific-text-sum-values/.

See our other posts on VLOOKUP:

IFERROR then perform another function – example

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