The IFERROR function evaluates an expression and returns the result obtained if it is not an error. Otherwise (if the evaluated expression returns an error), the function returns the result of evaluating the expression included as the second argument.
This function basically makes it possible to ensure that the result to be returned is never going to be an error (with the nuances indicated below), serving to catch and properly manage errors in the evaluation of expressions.
IFERROR(
value,
value_if_error
)
- value: Value or expression to evaluate, the result of which will be returned by IFERROR if no error occurs.
- value_if_error: Value or expression to return in the event that the evaluation of value returns an error.
IFERROR returns a value whose type will be that of the arguments passed to the function.
The IFERROR function is based on the IF function, but uses fewer arguments:
Both value and value_if_error must be of the same type. If, for example, the average sales per month are being calculated, we can return a 0 in the case that the denominator (the number of months) is 0 (which would cause a calculation error):
A Blank could also be returned:
Or even another expression:
But an attempt to return a value of another type (Boolean, text, etc.) causes an error:
The following example uses a text function (LEFT) in which an error is forced by specifying a negative value as the number of characters to extract, and the IFERROR function returns the text string passed as the second argument:
Of course, nothing prevents us from including as an argument to return in case of error a function that can also give an error during its evaluation (that is, the IFERROR function does not always ensure a non-erroneous result). The following example uses the same evaluated expression as the expression to return in case of error (but it could be any other):
It should be noted that being able to ensure the return of a "non-erroneous" value (by adding the second argument) does not resolve the reason why the original error occurred. That is why the IFERROR function must serve to capture errors and their correct management, not only to hide them.
The following example uses the SEARCH function to find the first occurrence of the letter "a" in the Manufacturer column that contains manufacturer names, and the MID function to return three characters from that position. The MID function is included as the first argument of the IFERROR function so that, in those cases where no letter "a" is found (occasions when the SEARCH function will return an error), a controlled result is returned (a Blank in this case).
Note that, in the last row, the letter "a" occupies the last position of the text string, so MID only returns that letter (which can be confirmed by viewing the length of the extracted text).
