IFERROR

What does IFERROR do?

Often when using formulas, if the result returns an error, you need to handle it, and if not, return the calculation result.

This is exactly the task that the IFERROR function solves.

The function checks the input value/calculation for errors, and if there are no errors, returns it as is.

The following error types are evaluated: #N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, and #NULL!

An equivalent combination would be a formula based on the combination of the IF function and ISERR.

Such a combination uses computational resources twice – once to check the result for errors, and a second time to perform the calculation if there are no errors.

IF(ISERR(calculation),"result-if-error",calculation)

The difference with IFERROR is that it performs the calculation only once and saves resources.

Using the function also simplifies formula syntax.

Syntax

The IFERROR function syntax requires only two arguments, both mandatory:

=IFERROR(value_or_calculation, value_if_error)

Example 1: IFERROR + VLOOKUP

The most characteristic example of usage is in combination with the VLOOKUP function when searching for data in large tables.

Option “if error, then custom value”:

=IFERROR(VLOOKUP(cell_or_value, range, column_number, 0), "error")

Option “If error, then empty”:

=IFERROR(VLOOKUP(cell_or_value, range, column_number, 0), "")

Since VLOOKUP can significantly load the processor, the IFERROR function is very useful here.

Example 2: IFERROR + division by zero

A marketer’s task is to evaluate the effectiveness of advertising campaigns. One of the key indicators is customer acquisition cost. It’s calculated quite simply – advertising campaign expenses are divided by the number of customers they brought.

What to do when a campaign didn’t bring any customers? The calculation will return an error:

#DIV/0!

It’s not advisable to leave this error, since money was spent on advertising and this needs to be accounted for.

The best option is to assume that one customer was acquired, because sooner or later this will happen, and effectiveness needs to be evaluated now. The IFERROR function allows you to return the entire campaign expense if a division by zero error occurs.

The most visual benefit of such a formula is when using conditional formatting with a color scale. Effective and ineffective campaigns immediately stand out.

Example 3: IFERROR in array formulas

The problem with MIN and MAX functions when working with arrays is that if there’s at least one error in the array, they also return an error.

Therefore, if there’s a possibility of such an event, the IFERROR function should be used as a handler to avoid resulting errors.

Formula to find the position of the first Latin character:

={MIN(IFERROR(SEARCH(CHAR(ROW(65:90)), A1), ""))}

Its mechanics work as follows:

• CHAR function creates an array of English alphabet letters
• SEARCH function looks for the position of each letter in the string
• If the letter is not found, the function returns #N/A error
• IFERROR function returns an empty string in such cases, and leaves numbers as they are
• MIN function skips empty strings and returns the minimum number
• If the entire array consists of empty strings, the MIN function returns 0

This formula uses a constant array and finds the position of the first digit:

={MIN(IFERROR(SEARCH({1,2,3,4,5,6,7,8,9,0}, A1), ""))}

Read more in the article about array formulas.

Other Logical functions in Excel

IF, AND, OR, NOT, IFERROR, IFS, SWITCH, ISERR, ISERROR

This post is also available in RU.