IfError() Function

IfError() Function

The Excel IFERROR function returns a custom result when a formula generates an error, and a standard result when no error is detected. IFERROR is an elegant way to trap and manage errors without using more complicated nested IF statements.

Syntax

The syntax for the IFERROR function in Microsoft Excel is:

=IFERROR( formula, alternate_value )

Parameters or Arguments

formula

The formula or value that you want to test.

value_if_error

The alternate value that is returned if the formula results in an error value (#N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME? or #NULL). Otherwise, the function will return the result of the formula if no error occurs.

Excel Iferror Function Examples

Example 1

The following spreadsheet shows two simple examples of the Excel Iferror function.

Formulas:

	A	B	C
1	1	2	=IFERROR( A1 / B1, 0 )
2	1	0	=IFERROR( A2 / B2, 0 )

Results:

	A	B	C
1	1	2	0.5	- A1 / B1 produces no error so result 0.5 is returned
2	1	0	0	- A2 / B2 produces an error so the alternative value 0 is returned

Note that:

-          In the first example (in cell C1), the value argument, A1/B1 returns the value 0.5. This is not an error and so this value is returned by the Iferror function.

-          In the second example (in cell C2), the value argument, A2/B2 returns the #DIV/0! error. Therefore, the Iferror function returns the value of the value_if_error argument, which is 0.

Example 2

In the following spreadsheet the Excel Iferror function is used with the Vlookup function.

If the Vlookup function successfully looks up a value, this is displayed in the cell; Otherwise, the text "not found" is displayed.

Formulas:

	A	B	C	D
1	Lookup List		Jim's Class:	=IFERROR( VLOOKUP( "Jim", A2:B6, 2, FALSE ), "not found" )
2	Beth	Class 1	Mary's Class:	=IFERROR( VLOOKUP( "Mary", A2:B6, 2, FALSE ), "not found" )
3	Bob	Class 2
4	Alf	Class 2
5	Jim	Class 3
6	Ann	Class 3

Results:

	A	B	C	D
1	Lookup List		Jim's Class:	Class 3
2	Beth	Class 1	Mary's Class:	"not found"
3	Bob	Class 2
4	Alf	Class 2
5	Jim	Class 3
6	Ann	Class 3

Notes:

-          If formula is empty, it is evaluated as an empty string ("") and not an error.

-          If value_if_error is supplied as an empty string (""), no message is displayed when an error is detected.

-          If IFERROR is entered as an array formula, it returns an array of results with one item for each cell in value.