ISERROR function in Excel with formula examples

This tutorial explores the practical applications of Excel's ISERROR function, demonstrating how to identify and manage errors within formulas. When a formula is invalid or uncalculatable, Excel displays an error message. The ISERROR function helps detect these errors and offers alternative solutions.

• ISERROR Function
• ISERROR Formula
• IF ISERROR
• IF ISERROR with VLOOKUP
• IF ISERROR with INDEX MATCH
• IF ISERROR Yes/No Formula
• Counting Errors
• ISERROR vs. IFERROR

The ISERROR Function

Excel's ISERROR function identifies various error types, including #CALC!, #DIV/0!, #N/A, #NAME?, #NUM!, #NULL!, #REF!, #VALUE!, and #SPILL!. It returns a Boolean value: TRUE for an error, FALSE otherwise. This function is compatible with Excel 2000 through 2021 and Excel 365.

The syntax is straightforward:

ISERROR(value) where value represents the cell or formula to be checked.

Using the ISERROR Formula

A basic ISERROR formula checks a cell for errors:

=ISERROR(A2)

This returns TRUE if an error exists in cell A2, and FALSE otherwise.

The IF ISERROR Formula

Combine ISERROR with the IF function to customize error handling. The general formula is:

IF(ISERROR(formula(...)), text_or_calculation_if_error, formula())

This means: if the main formula produces an error, display the specified text or perform a different calculation; otherwise, return the formula's normal result.

The image below shows a Price column with errors resulting from division:

To replace error codes with custom text, use:

=IF(ISERROR(A2/B2), "Unknown", A2/B2)

Excel 2007 and later offer the IFERROR function for a similar outcome:

=IFERROR(A2/B2, "Unknown")

IFERROR is slightly faster as it calculates A2/B2 only once, unlike IF ISERROR which calculates it twice.

IF ISERROR with VLOOKUP

Using ISERROR with VLOOKUP is a specific application of the IF ISERROR formula. When VLOOKUP fails (e.g., lookup value not found), display custom text using:

IF(ISERROR(VLOOKUP(...)), "custom_text", VLOOKUP(...))

This example retrieves times from a lookup table (D3:E10) to the main table (A3:B15). If a participant's name is not found, "Not qualified" is returned:

=IF(ISERROR(VLOOKUP(A3, $D$3:$E$10, 2, FALSE)), "Not qualified", VLOOKUP(A3, $D$3:$E$10, 2, FALSE))

Note: For handling only #N/A errors (lookup value not found), use IFNA (Excel 2013 ) or IF ISNA (older versions).

IF ISERROR with INDEX MATCH

The same error-handling technique applies to INDEX MATCH lookups. ISERROR checks for errors, and IF displays specified text if an error occurs:

IF(ISERROR(INDEX(return_column, MATCH(lookup_value, lookup_column, 0))), "custom_text", INDEX(return_column, MATCH(lookup_value, lookup_column, 0)))

This example uses INDEX MATCH to retrieve times from column D:

=IF(ISERROR(INDEX($D$3:$D$10, MATCH(A3, $E$3:$E$10, 0))), "Not qualified", INDEX($D$3:$D$10, MATCH(A3, $E$3:$E$10, 0)))

Again, consider using IFNA or IF ISNA for handling only #N/A errors.

IF ISERROR Yes/No Formula

This example returns different text based on the presence or absence of an error:

IF(ISERROR(formula(...)), "text_if_error", "text_if_no_error")

This formula checks if a participant's name is in the qualified list (column D) and returns "Yes" or "No":

=IF(ISERROR(MATCH(A3, $D$3:$D$10, 0)), "No", "Yes")

Counting Errors

To count errors in a column, use ISERROR on a range, convert Boolean values to 1s and 0s using the double unary operator (--), and sum the results using SUM or SUMPRODUCT:

=SUM(--ISERROR(C2:C10)) (Excel 365 and 2021)

{=SUM(--ISERROR(C2:C10))} (Array formula for Excel 2019 and earlier)

=SUMPRODUCT(--ISERROR(C2:C10)) (Works in all versions)

ISERROR vs. IFERROR

Both functions handle errors, but ISERROR only tests for errors, while IFERROR suppresses them and returns a specified value. IFERROR is available from Excel 2007 onwards. IF ISERROR offers more flexibility for handling both error and non-error scenarios.

Downloadable examples: ISERROR formula examples (.xlsx file)

The above is the detailed content of ISERROR function in Excel with formula examples. For more information, please follow other related articles on the PHP Chinese website!