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.


Reference:


No comments:

Post a Comment

OR Function