Skip to main content
Excel Cookbook
ExcelIFERRORError Handlingformula errorsdata cleaning

The Problem: Ugly Errors Everywhere

You've built a beautiful report with VLOOKUP formulas pulling prices from a product catalog. Everything looks perfect — until someone adds a new order with a Product ID that doesn't exist in the catalog. Suddenly, your report is littered with ugly #N/A errors. Your boss sees it and thinks your spreadsheet is broken.

Every Excel user has faced this: formulas that work perfectly 99% of the time but produce frightening error codes for edge cases. IFERROR is your safety net, catching those errors before anyone sees them.

The Ingredients: Understanding IFERROR's Setup

IFERROR is like a try-catch block in programming. It runs your formula, and if anything goes wrong, it gracefully returns a fallback value instead.

=IFERROR(value, value_if_error)

Parameter Description
value The formula or expression to evaluate (e.g., a VLOOKUP).
value_if_error What to return if value produces an error (e.g., 0, "", "N/A").

Important: IFERROR catches ALL error types: #N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, and #NULL!.

The Recipe (Step-by-Step): Wrapping VLOOKUP

Scenario: You have a VLOOKUP that sometimes returns #N/A:

=VLOOKUP(A2, Products!$A:$C, 3, FALSE)

Step 1: Wrap it in IFERROR:

=IFERROR(VLOOKUP(A2, Products!$A:$C, 3, FALSE), "Not Found")

Now, instead of #N/A, it shows "Not Found" — clean and clear.

Step 2: For numeric columns, use 0 instead of text:

=IFERROR(VLOOKUP(A2, Products!$A:$C, 3, FALSE), 0)

Step 3: To show a blank cell on error:

=IFERROR(VLOOKUP(A2, Products!$A:$C, 3, FALSE), "")

Advanced Recipe: Division Without #DIV/0!

A common headache — dividing by zero:

=IFERROR(B2/C2, 0)

If C2 is 0 or empty, the result is 0 instead of the dreaded #DIV/0! error.

Pro Tips: Sharpen Your Skills

  • Don't over-use IFERROR: It hides ALL errors, including legitimate ones. If your formula has a real bug, IFERROR will mask it. Use it only when you expect certain errors.
  • Use IFNA for VLOOKUP: If you only want to catch #N/A (lookup not found) but still see other errors, use =IFNA(formula, fallback) instead.
  • Nest for multiple fallbacks: Chain IFERROR calls: =IFERROR(VLOOKUP(...table1...), IFERROR(VLOOKUP(...table2...), "Not in any table")).

Troubleshooting: Common Pitfalls

  • Hidden bugs: IFERROR catches everything. If your formula suddenly stops working, temporarily remove IFERROR to see the actual error.
  • Performance: Wrapping heavy formulas doubles calculation time (Excel evaluates the formula, then checks for errors). For large datasets, consider alternatives.
  • Blank vs Zero: "" (empty string) and 0 behave differently in SUM/AVERAGE calculations. Choose wisely.

Related Recipes