FormulaFix · Plain-English guides to Excel and Google Sheets formulas

How to Use the IFERROR Function in Excel (and Google Sheets)

What Is the IFERROR Function?

IFERROR is a built‑in error‑handling function that lets you replace the default error messages Excel (or Google Sheets) would display—such as #DIV/0!, #N/A, or #VALUE!—with a value of your choosing.

IFERROR(value, value_if_error)

If value returns a valid result, IFERROR passes it through unchanged; otherwise it returns value_if_error. This keeps worksheets tidy and prevents cascading errors in downstream calculations.

Availability

Platform First version Notes
Excel 2007 (desktop) – works in all later versions, including Excel 365 and Excel 2021
Google Sheets All versions Identical syntax

Basic Syntax and Usage

The syntax is the same in Excel (2007 +) and Google Sheets:

IFERROR(value, [value_if_error])
Argument Description
value The formula or expression to evaluate.
value_if_error (optional) The value to return if value results in an error. If omitted, a blank cell is returned.

How It Works

  1. Evaluate the value argument.
  2. If the result is not an error, IFERROR returns that result unchanged.
  3. If the result is an error, IFERROR returns value_if_error (or a blank).

Simple Example

A B
10 2
5 0
8 4

Formula (C2):

=IFERROR(A2/B2, "N/A")
C (Result)
5
N/A
2

Explanation: Row 2 would produce #DIV/0!, which is replaced by “N/A”.

Quick Tips

Common Error Types Handled by IFERROR

Error Meaning Typical cause
#DIV/0! Division by zero =A1/B1 when B1 is 0 or blank
#N/A Value not available Lookup functions can’t find a match
#VALUE! Wrong type of operand Adding text to a number
#REF! Invalid reference Deleting a column/row used in a formula
#NUM! Invalid numeric result SQRT(-1)
#NAME? Unrecognized name Misspelled function name
#NULL! Intersection of non‑intersecting ranges Using a space () instead of a comma in a range

Masking an error

=IFERROR(A2/B2, "Check divisor")
A B Result
10 2 5
8 0 Check divisor
5 Check divisor

Practical Examples

1️⃣ Protect a Division That Might Divide by Zero

A B
10 2
5 0
8 4

Formula (C2):

=IFERROR(A2/B2, "N/A")
C (Result)
5
N/A
2

2️⃣ Hide Errors From a Lookup

C D
Banana 12
Cherry 8
Apple 15
Grape 7

Formula (E2):

=IFERROR(VLOOKUP("Apple", C2:D5, 2, FALSE), "Not found")

Result: 15. Changing the lookup value to “Mango” returns “Not found”.

3️⃣ Combine IFERROR with TEXT for Clean Reporting

A B
10 2
5 0
8 4

Formula (C2):

=IFERROR(TEXT(A2/B2, "0.00%"), "Invalid")
C (Result)
500.00%
Invalid
200.00%

Advanced Tips: Nesting IFERROR and Mixing With Other Functions

Goal Formula (Excel) Sample Data Result
Fallback for a missing VLOOKUP =IFERROR(VLOOKUP(A2,$D$2:$E$6,2,FALSE),"Not found") A2 = Apple; D2:D6 = Banana, Cherry, Apple, Grape, Mango; E2:E6 = 5,7,3,9,2 3
Two‑column lookup with INDEX/MATCH =IFERROR(INDEX($F$2:$F$8,MATCH(G2,$E$2:$E$8,0)),"N/A") G2 = West; E2:E8 = East, West, North, South, East, West, North; F2:F8 = 10,15,12,8,11,14,13 15
Avoid #DIV/0! when dividing a sum by a count that may be zero =IFERROR(SUM(H2:H6)/COUNTIF(I2:I6,">0"),0) H2:H6 = 20,30,0,40,10; I2:I6 = 1,0,0,1,1 30 (100 ÷ 3)

Nesting Multiple IFERRORs

You can chain IFERROR calls to test several alternatives in order:

=IFERROR(A2/B2,
    IFERROR(VLOOKUP(A2,$D$2:$E$6,2,FALSE),
        "No data"))

When to Avoid Using IFERROR

Situation Why It May Be Problematic Better Approach
Large data sets (thousands of rows) IFERROR forces Excel to evaluate the expression twice, which can double calculation time. Use IFNA when you only need to catch #N/A, or restructure the formula to prevent the error.
Need to distinguish error types All errors are treated the same, losing diagnostic detail. Combine IF with ISERROR, ISNA, ISDIV0, etc., to handle each case separately.
Debugging / auditing Hiding errors can mask data‑quality problems. Temporarily remove IFERROR or use conditional formatting to highlight error cells.
Dynamic array formulas (Excel 365 / Google Sheets) IFERROR can interfere with spill behavior when the error occurs inside a spilled range. Capture intermediate results with LET and test them with IF/ISERROR before the spill.
Performance‑critical dashboards Repeated IFERROR around volatile functions (TODAY, RAND) adds recalculation load. Limit error handling to the outermost formula and replace volatile functions with static values when possible.

Quick checklist

FAQ

What errors does IFERROR catch?

IFERROR catches any standard spreadsheet error: #DIV/0!, #N/A, #VALUE!, #REF!, #NUM!, #NAME?, and #NULL!. It treats them all the same, returning the value_if_error you specify.

Can I use IFERROR in Google Sheets?

Yes. Google Sheets supports IFERROR with identical syntax to Excel, and it works in all current versions of Sheets.

Is there an alternative to IFERROR in older Excel versions?

Before Excel 2007, IFERROR isn’t available. You can emulate its behavior with IF(ISERROR(...), value_if_error, ...). For handling only #N/A, the IFNA function (Excel 2013+) or IF(ISNA(...), …) works as well.

All formulas shown are syntactically valid for the indicated platforms.

Related guides