How to use IFERROR in Excel with formula examples

This tutorial demonstrates how Excel's IFERROR function handles errors, replacing them with blanks, alternative values, or custom messages. It covers using IFERROR with VLOOKUP and INDEX MATCH, and compares it to IF ISERROR and IFNA.

"Give me a place to stand, and I will move the earth," Archimedes famously declared. An Excel user might similarly say, "Give me a formula, and I'll make it throw an error!" This tutorial focuses not on creating errors, but on preventing them for cleaner, more transparent worksheets.

Excel's IFERROR Function: Syntax and Basic Usage

IFERROR is designed to manage formula errors. It checks a formula; if an error occurs, it returns a specified value; otherwise, it returns the formula's result.

Syntax:

IFERROR(value, value_if_error)

• Value: The formula, expression, value, or cell reference to check.
• Value_if_error: The replacement value if an error is detected (blank, text, number, another formula, etc.).

For example, division can produce various errors if a divisor is zero or a cell is blank. IFERROR prevents this.

Handling Errors:

• Blank Cell: =IFERROR(A2/B2, "") returns a blank cell if an error occurs.

  

• Custom Message: =IFERROR(A2/B2, "Calculation Error") displays a custom message.

  

Key IFERROR Characteristics:

1. Handles all error types (#DIV/0!, #N/A, #NAME?, #NULL!, #NUM!, #REF!, #VALUE!).
2. Replaces errors with custom text, numbers, dates, logical values, other formulas, or blanks.
3. Treats blank cells as empty strings, not errors.
4. Available from Excel 2007 onwards.
5. For older Excel versions (2003 and earlier), use IF with ISERROR.

IFERROR Formula Examples

These examples show IFERROR with other functions.

IFERROR with VLOOKUP:

VLOOKUP often returns #N/A if a lookup value isn't found. IFERROR provides a user-friendly alternative:

=IFERROR(VLOOKUP(A2, 'Lookup table'!$A$2:$B$4, 2,FALSE), "Not found")

(Note: IFNA is preferable if only #N/A errors need handling.)

Nested IFERROR for Sequential VLOOKUPs:

For multiple VLOOKUPs across different sheets, nested IFERRORs provide a solution:

=IFERROR(VLOOKUP(A2,'Report 1'!A2:B5,2,0),IFERROR(VLOOKUP(A2,'Report 2'!A2:B5,2,0),IFERROR(VLOOKUP(A2,'Report 3'!A2:B5,2,0),"not found")))

IFERROR in Array Formulas:

Array formulas perform multiple calculations. IFERROR can handle errors within these calculations:

=SUM(IFERROR($B$2:$B$4/$C$2:$C$4,0)) (Enter as an array formula: Ctrl Shift Enter)

IFERROR vs. IF ISERROR:

IFERROR is simpler and more efficient than the older IF(ISERROR(...)) combination, which requires repeating the main formula.

IFERROR vs. IFNA:

IFNA handles only #N/A errors, while IFERROR handles all error types. Choose IFNA when you want to highlight other errors but suppress only #N/A.

Best Practices:

• Avoid unnecessary error trapping.
• Apply IFERROR to the smallest relevant part of a formula.
• Use more specific functions (IFNA, ISERR) when appropriate.

This tutorial provides a comprehensive guide to using Excel's IFERROR function for effective error handling. A sample workbook is available for further exploration.

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