Catch and Replace Any Error in Excel and Google Sheets
Purpose
IFERROR is the formula I add to almost every formula that could possibly fail in a shared workbook. A VLOOKUP…
IFERROR is the formula I add to almost every formula that could possibly fail in a shared workbook. A VLOOKUP that returns #N/A when the code does not exist. A division that returns #DIV/0! when the denominator is zero. An INDEX MATCH that errors when the lookup table is on a different sheet that was renamed. These errors make spreadsheets look broken to anyone reading them and can cascade — one #N/A flowing into a SUM causes the entire sum to error. IFERROR wraps around the formula and intercepts any error before it reaches the cell, replacing it with whatever you choose. The trade-off is that it catches all error types including genuine formula mistakes, so a broken formula hidden behind Not found is harder to diagnose. For lookup-specific error handling where only #N/A should be caught and real formula errors should remain visible, IFNA is the more surgical choice — it is closely related to the VLOOKUP with IFERROR pattern and uses the ISERROR function under the hood. But for clean output in reports and dashboards, IFERROR is the standard tool.
Syntax
✓ Excel 2007+
✓ Google Sheets
= Same syntax
=IFERROR(value, value_if_error)
Arguments
| Argument | Required | Description |
|---|---|---|
| value | Required | The formula to evaluate. If this produces any error, value_if_error is returned instead. If it produces a valid result, that result is returned. |
| value_if_error | Required | What to return when value produces any error. Can be a text string, 0, an empty string, or another formula. |
How it works
IFERROR evaluates its first argument. If the result is a value — any number, text, date, boolean, or even an error from a different formula deeper in the chain — IFERROR passes it through unchanged. Only when the first argument itself produces one of Excel's error types — #N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, #NULL! — does IFERROR substitute value_if_error. The substitution is seamless. The cell displays the fallback as if the original formula had returned it. Error types and when they typically appear: #N/A from lookup functions when a value is not found; #DIV/0! from division when the denominator is zero; #VALUE! from type mismatches; #REF! from broken cell references; #NAME? from misspelled function names. For catching only #N/A from lookups while leaving other errors visible, use the IFNA function instead, which is available in Excel 2013 and newer.
Examples
1
Wrap a VLOOKUP in IFERROR to show Not found instead of #N/A when a product code does not exist in the reference table.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Code | Product | Code | Product |
| 2 | PRD-002 | =IFERROR(VLOOKUP(A2,$D$2:$E$7,2,FALSE),"Not found") | PRD-001 | Mouse |
| 3 | PRD-099 | Not found | PRD-002 | Keyboard |
| 4 | PRD-004 | Laptop Bag | PRD-003 | Monitor |
| 5 | PRD-004 | Laptop Bag | ||
| 6 | PRD-005 | Cable |
Row 2: Keyboard
— PRD-002 is found in the reference table so VLOOKUP returns Keyboard. IFERROR passes it through unchanged.
PRD-099 is not in the table so VLOOKUP returns #N/A. IFERROR intercepts it and shows Not found. Without IFERROR, the red error would appear in the cell and break any formula referencing it downstream.
2
Handle divide-by-zero errors in a profit margin calculation by returning 0 when revenue is zero.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Product | Profit | Revenue | Margin |
| 2 | Mouse | 8.50 | 29.99 | =IFERROR(B2/C2,0) |
| 3 | Keyboard | 12.00 | 0 | 0 |
| 4 | Monitor | 45.00 | 289.99 | 0.155 |
| 5 | Cable | 3.20 | 12.99 | 0.246 |
| 6 | Webcam | 0 | 0 | 0 |
Row 2: 0.283
— Mouse: 8.50 / 29.99 = 0.283. Keyboard has revenue of 0 so B3/C3 would be #DIV/0! — IFERROR returns 0 instead. Webcam also has zero revenue and zero profit.
Using 0 as the fallback keeps the margin column fully numeric so AVERAGE, SUM, and charts all work without errors. A text fallback like N/A would break any downstream numeric formula referencing this column.
3
Use an empty string as the fallback so cells with errors appear blank rather than showing text or zero.
| A | B | |
|---|---|---|
| 1 | Code | Department |
| 2 | EMP-001 | =IFERROR(VLOOKUP(A2,$D$2:$E$7,2,FALSE),"") |
| 3 | EMP-099 | |
| 4 | EMP-003 | Sales |
| 5 | EMP-050 | |
| 6 | EMP-004 | Finance |
Row 2: Engineering
— EMP-001 is found and Engineering is returned. EMP-099 and EMP-050 do not exist — IFERROR returns an empty string so those cells appear blank rather than showing an error or a Not found label.
Use "" as the fallback when you want missing values to be invisible — for example when printing a report where blank cells look cleaner than text placeholders. The cell is not truly blank (it contains a formula) but it displays as blank.
Common use cases
1. Replace #N/A from VLOOKUP or INDEX MATCH with a friendly label when a lookup value is not found
=IFERROR(VLOOKUP(A2,$D:$E,2,FALSE),"Not found")
2. Return 0 instead of #DIV/0! in a percentage or ratio calculation when the denominator is zero
=IFERROR(B2/C2,0)
3. Leave cells blank instead of showing errors in a clean printed report
=IFERROR(formula,"")
4. Chain a second lookup as the fallback when the primary lookup table has no match
=IFERROR(VLOOKUP(A2,Table1!A:B,2,FALSE),VLOOKUP(A2,Table2!A:B,2,FALSE))
5. Prevent #VALUE! from breaking a calculation when cells in the range occasionally contain text
=IFERROR(SUM(A2:A10*B2:B10),0)
Common errors
IFERROR shows the fallback even when the formula has a genuine mistake
IFERROR catches all errors including formula bugs like wrong column index or broken references. The fallback hides the error.
Fix: Use IFNA instead when only #N/A from not-found lookups should be caught — other errors will then remain visible for diagnosis.
The fallback value is the wrong data type for downstream formulas
A text fallback like Not found in a numeric column breaks SUM and AVERAGE on that column.
Fix: Match the fallback type to the column: use 0 for numeric columns and "" for display-only columns. Never use text in a column that feeds numeric aggregations.
IFERROR is not available
IFERROR was introduced in Excel 2007. In Excel 2003, use IF(ISERROR(formula),fallback,formula) which is the older equivalent.
Fix: For Excel 2003 compatibility, use =IF(ISERROR(VLOOKUP(...)),"Not found",VLOOKUP(...)) — note the formula must be written twice.
Tips and variations
Use IFNA for lookups to keep genuine errors visible
IFERROR catches everything including real mistakes. IFNA catches only #N/A — the specific error from lookup not-found. Switching to IFNA in lookup formulas means broken range references and wrong column indexes still show as errors, which is the behaviour I use in all production workbooks.
=IFNA(VLOOKUP(A2,$D:$E,2,FALSE),"Not found")
Match the fallback data type to the column purpose
Text fallbacks in numeric columns silently break downstream aggregations. Always use 0 for columns feeding calculations and "" or a label for display-only columns. The choice of fallback is a design decision, not just cosmetic.
=IFERROR(B2/C2,0)
Chain a second formula as the fallback for two-source lookups
Put a second VLOOKUP or INDEX MATCH as value_if_error to create an automatic two-table fallback. The primary lookup runs first and only if it fails does the secondary run — no helper columns needed.
=IFERROR(VLOOKUP(A2,Primary!A:B,2,FALSE),VLOOKUP(A2,Backup!A:B,2,FALSE))
Excel vs Google Sheets
Excel vs Google Sheets
IFERROR works identically in Excel 2007 and newer and every version of Google Sheets. The syntax and error-catching behaviour are the same in both applications. In Excel 2003, use IF(ISERROR(...)) as the equivalent pattern.
Related reading
Frequently asked questions
IFERROR evaluates a formula and returns its result if no error occurs. If the formula produces any error — #N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, or #NULL! — IFERROR returns the value_if_error argument instead. It is the standard way to replace error messages with friendly text, zero, or a blank in any formula result.
IFERROR is a single formula that evaluates the expression and returns the fallback in one step — the result is the value or the fallback. ISERROR is a check function that returns TRUE or FALSE, usually used inside an IF: =IF(ISERROR(formula),fallback,formula). IFERROR is shorter and more readable. ISERROR is useful when you need the error check as a logical value for a more complex formula.
Yes. IFERROR catches every Excel error type: #N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, and #NULL!. This breadth is both its strength and its risk — it also hides genuine formula mistakes. For lookup formulas where only #N/A should be caught, use IFNA instead which catches only #N/A and leaves other errors visible.
Anything that makes sense for the context. Common choices are: a text string like Not found or Error for display columns; 0 for numeric columns feeding into SUM or AVERAGE; an empty string to leave the cell visually blank; or another formula that provides a secondary calculation or lookup as a fallback.
Wrap the division in IFERROR: =IFERROR(A2/B2,0) returns 0 when B2 is zero instead of showing #DIV/0!. For a percentage calculation where the denominator might be zero, =IFERROR(A2/B2,"N/A") is a clean solution that keeps the report readable.