How to Use IFERROR in Excel (Catch Formula Errors + Free Generator)

How to Use IFERROR in Excel and Google Sheets

IFERROR is one of the most important functions for creating robust spreadsheets. It catches errors like #DIV/0!, #N/A, #VALUE!, and #REF! and replaces them with a clean value — keeping your reports professional and your calculations running smoothly.

IFERROR Syntax

The syntax is simple: =IFERROR(value, value_if_error)

• value: The formula, cell reference, or expression you want to evaluate.
• value_if_error: What to return if the first argument produces any error.

Step-by-Step: Protect a Division Formula

Imagine you have a sales commission table:

Without IFERROR, Bob and Charlie's rows show ugly errors. Here's how to fix it:

1. Write your original formula: =B2*C2 — this calculates commission by multiplying the sales amount by the rate.
2. Wrap with IFERROR: =IFERROR(B2*C2, 0) — if the multiplication fails (e.g., C2 is blank), show 0 instead of #DIV/0!.
3. Copy down the column: Drag the formula to all rows. Every row now shows either a valid commission or 0 — no errors visible.

The result: clean, professional-looking data with no explanation needed.

IFERROR with VLOOKUP and XLOOKUP

One of the most common uses of IFERROR is to handle lookup failures gracefully:

• VLOOKUP + IFERROR: =IFERROR(VLOOKUP(A2, B:C, 2, FALSE), "Not Found") — shows "Not Found" instead of #N/A when an employee ID has no match.
• XLOOKUP alternative: XLOOKUP has a built-in if_not_found argument: =XLOOKUP(A2, B:B, C:C, "Not Found"). This is cleaner than wrapping in IFERROR.
• INDEX MATCH + IFERROR: =IFERROR(INDEX(C:C, MATCH(A2, B:B, 0)), "Missing") — same protection for the INDEX MATCH combination.

IFERROR vs IFNA — When to Use Which

Common Pitfalls and Edge Cases

• Masking bugs: IFERROR hides ALL errors. If your VLOOKUP is returning #REF! because a column was deleted, IFERROR silently covers it up. Always debug the inner formula first before wrapping with IFERROR.
• Blank fallback: Use "" (empty string) to leave the cell blank on error: =IFERROR(A1/B1, ""). This looks clean in reports but remember the cell is not truly empty — it contains a formula returning "".
• Nested IFERROR: You can nest IFERROR calls to try multiple strategies: =IFERROR(VLOOKUP(A2, Sheet1!A:B, 2, 0), IFERROR(VLOOKUP(A2, Sheet2!A:B, 2, 0), "Not in any sheet")) — tries Sheet1 first, then Sheet2, then gives up.
• Performance: IFERROR evaluates the first argument completely. If the inner formula is slow (e.g., an array lookup over 100k rows), IFERROR doesn't speed it up — it just cleans the result.