Excel IFERROR Function


What does it do?

The Excel IFERROR function evaluates a formula or cell reference and allows for one of two returns.  Firstly, if there is no error, you will see the value from your formula or cell reference.  Secondly, if there is an error, typically in the form of #N/A, #VALUE! or #DIV/0!, you can customise the value-if-error return.  The value-if-error return can range from displaying nothing, a number, text or any other formulation.


Syntax Explained

Excel IFERROR function in the formula bar showing syntax.

Value: This is the formula or cell reference to be evaluated if no error.

Value_if_error: This is the value to return, or formula to be evaluated if the value above is an error.


Examples

1. IFERROR if the evaluated value from a VLOOKUP is #N/A and you want to return text.

Example of using an Excel IFERROR with VLOOKUP function to customise an evaluation when an error is returned.

The IFERROR function works with many formulas, so it’s not limited to the ones in these examples.  The first common example is looking for a value in the range, if it cannot find it, the VLOOKUP will evaluate as #N/A.  You can give a more appealing and descriptive evaluation in the form of “Not Found”, as per the example, above.

2. IFERROR if the evaluated value from a FIND function is #VALUE! and you want to return text.

Example of using an Excel IFERROR with FIND function to customise an evaluation when an error is returned.

Much the same as the previous example, the FIND function cannot locate the lookup text “Black” in the range, it will evaluate this as #VALUE!.

3. IFERROR if the evaluated value is #DIV/0! and you want to show BLANK.

Example of using an Excel IFERROR function on division so that the error evaluations can be returned as a BLANK.

The IFERROR function can be used to show BLANK on those evaluations when the formula is trying to divide by zero, when attempting to, the formula will evaluate to #DIV/0!.  This can neatly be removed by expressing it as double quotes, as shown above.

Pro Tip

Use the IFERROR function as a tidier IF function when error checking.  There was a time I didn’t know about IFERROR and I used to write IF(formula = #N/A, if TRUE show BLANK, if FALSE do formula).  This makes the entire formula longer than is needed, also, you’d need to edit it in two places should there be a need to down the line.