|
|
IFERRORX Function
Use this function for trapping and handling errors in formulas. It is similar to the IFERROR function available in Excel 2007 and later Excel versions, but also works with earlier Excel versions and has options to pass through #N/A and #Value.
Description
Returns a value you choose if the formula expression returns an error, otherwise returns the result of the formula expression.
Sometimes you need to be able to trap all error values except #N/A or #VALUE so that the error value is allowed to propagate through dependent formulas. So IFERRORX allows you to optionally request that #N/A and/or #Value are NOT considered as errors by IFERRORX but are passed through instead.
Its more efficient to use IFERRORX instead of formulas like =IF(ISERROR(VLOOKUP(A1,B1:C65000,2,FALSE)),”Not Found”, VLOOKUP(A1,B1:C65000,2,FALSE))
IFERRORX is a multi-threaded, non-volatile array function.
IFERRORX Syntax
IFERRORX (TheExpression, Value_if_error, Exclude_NA, Exclude_Value)
TheExpression – Required
The value of formula expression to be checked for an error.
Value_if_error – Required
The value to return if Expression evaluates to an error.
Exclude_NA – Optional
If TRUE and the error is #N/A do NOT evaluate as an error and so return #N/A. Default FALSE.
Exclude_Value –Optional
If TRUE and the error is #VALUE do NOT evaluate as an error and so return #VALUE. Default FALSE.
Remarks
If the IFERRORX function is contained in an array formula and TheExpression returns an array of values then IFERRORX handles each value in the array individually.
If TheExpression evaluates to an empty cell IFERRORX treats it as a zero. This behaviour is not the same as the IFERROR function in Excel 2007 and later which treats an empty cell as an empty string.
The IFERRORX function is thread-safe.
Examples
=IFERRORX(VLOOKUP(A1,B1:C65000,2,FALSE),”Not Found”) instead of =IF(ISERROR(VLOOKUP(A1,B1:C65000,2,FALSE)),”Not Found”, VLOOKUP(A1,B1:C65000,2,FALSE))
=IFERRORX(200/5,”Division Error”) returns 40
=IFERRORX(200/0,”Division Error”) returns “Division error”
=IFERRORX(MATCH(99,{1,2,3,4,5,6,7,8,99,100},0),0) returns 9 (the 9th position)
=IFERRORX(MATCH(99,{1,2,3,4,5,6,7,8,9},0),0) returns 0 (99 is not found so the #N/A is replaced by Value_if_error)
=IFERRORX(MATCH(99,{1,2,3,4,5,6,7,8,9},0),0,TRUE) returns #N/A (Exclude_NA is TRUE so the #N/A is not considered an error)
|
|
