Logical

IFERROR Function in Excel

Returns a specified value if a formula evaluates to an error; otherwise, returns the result of the formula.

Syntax

  • =IFERROR(value, value_if_error)

Arguments

  • value (required): The formula to check for errors
  • value_if_error (required): Value to return if error occurs

Examples

  • =IFERROR(A1/B1, 0) - Return 0 if division causes error - Result: Result or 0
  • =IFERROR(VLOOKUP(A1, Data, 2, FALSE), "Not Found") - Handle lookup errors - Result: Value or Not Found

IFERROR best practices

  • Use IFERROR for user-friendly output after you understand the underlying error.
  • Avoid hiding errors during model building because it can mask broken references or bad data.

Excel IFERROR function documentation

  • IFERROR is a Logical function that catches any Excel error and returns a custom value instead.
  • Syntax: =IFERROR(value, value_if_error). Available in Excel 2007 and later.
  • Common use: wrap lookup formulas to show "Not found" instead of #N/A — but fix the root cause first.
  • Related: [IFNA](/functions/ifna/) (only #N/A), [IF](/functions/if/) for logic tests, [ISERROR](/functions/iserror/) for diagnostics.

IFERROR syntax and when to use it

  • value (required): the formula or expression to evaluate — often a VLOOKUP, XLOOKUP, or division.
  • value_if_error (required): text, number, blank "", or another formula returned on any error.
  • IFERROR catches #N/A, #VALUE!, #REF!, #DIV/0!, #NAME?, #NUM!, and #NULL!.
  • Example: =IFERROR(VLOOKUP(A2,Table,3,FALSE), "Missing") shows friendly text when lookup fails.

Step-by-step: add IFERROR without hiding bugs

  • Step 1 — Build and test the core formula without IFERROR until it works on known rows.
  • Step 2 — Spot-check three IDs that should match and one that should not.
  • Step 3 — Wrap only the final expression: =IFERROR(your_formula, "Not found").
  • Step 4 — Avoid wrapping entire rows of unrelated calculations — narrow the scope.
  • Step 5 — For lookup-only #N/A, prefer [IFNA](/functions/ifna/) so #REF! and #VALUE! stay visible.

IFERROR vs IFNA vs XLOOKUP if_not_found

  • IFERROR — catches every error type; can hide broken references you still need to fix.
  • IFNA — catches only #N/A; best for VLOOKUP/XLOOKUP/MATCH when other errors must remain visible.
  • XLOOKUP if_not_found — built-in fourth argument; may eliminate wrapper formulas in M365.
  • Problem guide: [IFERROR not working as expected](/problems/iferror-not-working/).

Worked examples to copy

  • Lookup safety net: =IFERROR(XLOOKUP(A2,IDs,Prices), 0).
  • Division guard: =IFERROR(A2/B2, "") avoids #DIV/0! on blank denominators.
  • Nested calculation: =IFERROR(AVERAGEIF(range,">0"), "No data").
  • Do not use: =IFERROR(A1/B1, A1/B1) — masks errors without changing the result.

People also ask

  • Should I wrap every formula in IFERROR? — No. Fix logic first; use IFERROR for user-facing output only.
  • IFERROR vs IFNA for VLOOKUP? — IFNA is safer when you only want to catch #N/A.
  • Does IFERROR slow Excel? — Minimal on small models; avoid wrapping huge array blocks unnecessarily.
  • Can IFERROR return another formula? — Yes. value_if_error can be any valid expression.

Common errors

  • Catches ALL error types
  • May hide legitimate errors you should fix

Use cases

  • Error handling
  • Clean reports
  • Robust formulas
  • User-friendly outputs

Frequently asked questions

  • What errors does IFERROR catch? IFERROR catches ALL Excel errors: #N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, #NULL!. If you only want to catch #N/A (common in lookups), use IFNA instead to let other errors show.
  • Should I always use IFERROR? No! IFERROR can hide legitimate errors that indicate formula problems. Use it strategically for expected errors (like lookup not found) but let unexpected errors show so you can fix them.
  • What is the difference between IFERROR and IFNA? IFERROR catches all errors. IFNA only catches #N/A errors. Use IFNA for lookups where #N/A is expected but other errors (#REF!, #VALUE!) indicate real problems you should see.
  • Can I nest IFERROR? Yes, for fallback logic: =IFERROR(VLOOKUP(A1,Table1,2,0), IFERROR(VLOOKUP(A1,Table2,2,0), "Not in either table")). This tries Table1 first, then Table2, then shows message.
  • Does IFERROR affect performance? IFERROR evaluates the formula twice if there's no error (once to check, once to return). For complex formulas, consider LET function to calculate once: =LET(result, formula, IFERROR(result, 0)).

Editorial review

  • Reviewed by Excel.Directory Editorial Team. Updated May 2026.

When to use IFERROR

  • Error handling — common Logical scenario for IFERROR.
  • Clean reports — common Logical scenario for IFERROR.
  • Robust formulas — common Logical scenario for IFERROR.
  • User-friendly outputs — common Logical scenario for IFERROR.

IFERROR in the Logical category

  • Browse all Logical functions at /categories/logical/ for related formulas.
  • IFERROR syntax: =IFERROR(value, value_if_error)
  • value (required): The formula to check for errors
  • value_if_error (required): Value to return if error occurs
  • Confirm IFERROR arguments match the syntax shown above before filling down.
  • Lock table and range references with $ when copying formulas across rows or sheets.

Formula checklist before you copy down

  • Confirm IFERROR arguments match the syntax shown above before filling down.
  • Lock table and range references with $ when copying formulas across rows or sheets.
  • If results look wrong, check for text stored as numbers and invisible spaces with TRIM.
  • Spot-check three known input rows manually against expected output.

Related Excel functions

  • IFNA (/functions/ifna/): Returns a value you specify if a formula returns #N/A; otherwise returns the formula result.
  • ISERROR (/functions/iserror/): Returns TRUE if the value is any error value.
  • ISNA (/functions/isna/): Returns TRUE if the value is the #N/A error.
  • ERROR.TYPE (/functions/error-type/): Returns a number corresponding to an error type.

Comparisons involving this function

  • XLOOKUP vs VLOOKUP (/compare/xlookup-vs-vlookup/): Use XLOOKUP for modern Excel workbooks when available. Use VLOOKUP when you need compatibility with older Excel versions or existing legacy files.
  • FILTER vs VLOOKUP (/compare/filter-vs-vlookup/): Use FILTER when you need multiple matching rows or dynamic results. Use VLOOKUP for a single value from a structured lookup table.

Common problems and fixes

  • How to Fix #SPILL! in FILTER (/problems/filter-spill-error/): Clear the spill range, unmerge cells, and make sure the FILTER output has enough empty space to expand.
  • Why Is My Excel Formula Not Calculating? (/problems/formula-not-calculating/): Press F9 or switch to Automatic calculation, re-enter the formula in General format, and confirm the referenced ranges still exist.
  • Why Is IFERROR Not Working? (/problems/iferror-not-working/): Wrap the exact expression that produces the error, use IFNA for #N/A only, and fix the source formula before hiding the error message.
  • How to Fix a Circular Reference in Excel (/problems/circular-reference-warning/): Use Formulas > Error Checking > Circular References to locate the cell, then break the loop with a separate input cell or iterative calculation only when intentional.

Errors to watch for

  • Catches ALL error types — review causes on linked error pages in the directory.
  • May hide legitimate errors you should fix — review causes on linked error pages in the directory.

Copy-paste audit workflow

  • Enter IFERROR on three test rows with known expected output documented on a QA tab.
  • Fill down only after absolute references are locked on lookup tables and rate tables.
  • Compare against manual calculation or a calculator for financial and statistical functions.
  • Search this directory for comparison guides when choosing between similar functions in the same category.

IFERROR worked examples to copy

  • =IFERROR(A1/B1, 0) — Return 0 if division causes error. Expected result: Result or 0.
  • =IFERROR(VLOOKUP(A1, Data, 2, FALSE), "Not Found") — Handle lookup errors. Expected result: Value or Not Found.

IFERROR reference summary for crawlers and offline review

  • IFERROR belongs to the Logical category in Excel. Returns a specified value if a formula evaluates to an error; otherwise, returns the result of the formula.
  • Full syntax: =IFERROR(value, value_if_error). Open /functions/iferror/ for parameters, FAQs, and related pages.
  • Common mistakes: Catches ALL error types; May hide legitimate errors you should fix
  • Pair this function with comparison guides when another Excel formula might fit the same task better.
  • Review fix-excel-formula-errors when unexpected errors appear after upgrading Excel or sharing across locales.