IFERROR Function in Excel and Google Sheets
Purpose
Return a custom result instead of an error when a formula fails
Return value
The result of value if it does not produce an error, otherwise value_if_error
IFERROR is the function I add to almost every lookup and calculation formula before sharing a spreadsheet with anyone outside my immediate team. Nothing makes a report look unfinished, fragile, or confusing quite like a column full of #N/A or #DIV/0! errors — even when those errors are expected, technically correct, and completely explainable. A non-technical stakeholder who sees a column of #N/A errors does not think the formula is working as designed. They think the spreadsheet is broken. IFERROR gives you complete control over what your spreadsheet shows when a formula cannot produce a valid result. Replace the error with a blank cell to keep reports visually clean. Replace it with zero to keep totals numeric. Replace it with a message like Not found to make the situation self-explanatory. Or use it to chain a fallback formula — try one source first, and if that fails automatically try another. Two arguments, unlimited control over error presentation.
Syntax
✓ Excel 2007+
✓ Google Sheets
= Same syntax
=IFERROR(value, value_if_error)
For Excel 2003 and earlier use =IF(ISERROR(value),value_if_error,value) which evaluates the formula twice.
Arguments
| Argument | Required | Description |
|---|---|---|
| value | Required | The formula or expression to evaluate. IFERROR runs this first and checks if it produces an error. Typically a complete formula like VLOOKUP(...), A1/B1, or any expression that might error. |
| value_if_error | Required | What to return when value produces any error. Common choices: "" for blank, 0 for zero, "Not found" for lookups, or another formula to try as a fallback. |
How it works
IFERROR evaluates the value argument — which is typically a complete formula — and checks whether the result belongs to any of Excel's seven error types: #N/A, #VALUE!, #REF!, #DIV/0!, #NAME?, #NUM!, or #NULL!. If the formula produces a valid result of any kind, including zero, FALSE, or an empty string, IFERROR returns that result unchanged. If the formula produces any error, IFERROR returns the value_if_error argument instead and ignores the error entirely. Always place IFERROR as the outermost wrapper around a formula rather than nesting it inside. Write IFERROR(VLOOKUP(...),"Not found") not VLOOKUP(IFERROR(...),table,2,FALSE). A critical behaviour to keep in mind: IFERROR catches all errors without distinguishing between expected errors like missing lookup values and unexpected errors caused by formula bugs. Test the inner formula thoroughly without IFERROR before wrapping it, so you know that any errors being caught are the ones you deliberately designed the formula to handle.
Examples
1
Wrap VLOOKUP so missing product codes show "Not found" instead of #N/A.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Lookup Code | Code | Product Name | Price |
| 2 | PRD-003 | PRD-001 | Wireless Mouse | 29.99 |
| 3 | PRD-999 | PRD-002 | USB Keyboard | 45.99 |
| 4 | PRD-005 | PRD-003 | Monitor Stand | 89.50 |
| 5 | PRD-004 | Laptop Bag | 59.99 | |
| 6 | PRD-005 | HDMI Cable | 12.99 |
Row 2: Monitor Stand
— PRD-003 was found so VLOOKUP returns Monitor Stand — IFERROR passes the result through unchanged.
PRD-999 is not in the table so VLOOKUP returns #N/A. IFERROR catches it and returns "Not found" instead. Row 1 shows a normal result, row 2 shows the IFERROR fallback.
2
Divide sales by units sold, returning 0 when units is zero to avoid #DIV/0!
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Product | Sales | Units | Avg Price |
| 2 | Mouse | 2990 | 100 | =IFERROR(B2/C2,0) |
| 3 | Keyboard | 0 | 0 | 0 |
| 4 | Monitor | 4500 | 50 | 90.00 |
| 5 | Laptop Bag | 0 | 0 | 0 |
| 6 | Cable | 650 | 50 | 13.00 |
Row 2: 29.90
— 2990 / 100 = 29.90. No error so IFERROR returns the division result directly.
Keyboard and Laptop Bag have 0 units. Without IFERROR these would show #DIV/0! which breaks charts and SUM totals referencing this column.
3
Try a primary table first, fall back to a secondary table when not found.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Lookup | Table 1 Code | Name | Table 2 Code | Name2 |
| 2 | PRD-001 | PRD-001 | Wireless Mouse | ARCH-001 | Archive Mouse |
| 3 | ARCH-002 | PRD-002 | USB Keyboard | ARCH-002 | Archive Keyboard |
| 4 | PRD-003 | PRD-003 | Monitor Stand | ARCH-003 | Archive Stand |
Row 2: Wireless Mouse
— PRD-001 found in Table 1 so the first VLOOKUP succeeds and IFERROR returns its result.
ARCH-002 is not in Table 1 so the first VLOOKUP errors. IFERROR runs the second VLOOKUP against Table 2 which finds ARCH-002 and returns Archive Keyboard.
Common use cases
1. Show a blank cell instead of #N/A when a VLOOKUP or XLOOKUP lookup value is not found in the table
=IFERROR(VLOOKUP(A2,Table,2,FALSE),"")
2. Prevent #DIV/0! errors in a percentage calculation column when the denominator may be zero
=IFERROR(B2/C2,"")
3. Try a primary lookup table first and automatically fall back to a secondary archive table if not found
=IFERROR(VLOOKUP(A2,Primary!A:B,2,FALSE),VLOOKUP(A2,Backup!A:B,2,FALSE))
4. Return zero instead of #N/A from a MATCH function so the result can be used safely in arithmetic
=IFERROR(MATCH(A2,B:B,0),0)
5. Suppress errors in a SUM over a range that may contain VLOOKUP formulas returning #N/A
=SUM(IFERROR(D2:D50,0))
Common errors
Silently hiding genuine formula bugs
IFERROR catches every error without distinguishing between expected errors and genuine formula mistakes. A wrong range reference, a mistyped function name, or an incorrect argument will be silently replaced by the fallback value. The formula appears to work correctly but returns wrong data.
Fix: Always test the inner formula independently without IFERROR first. Confirm it works correctly on valid data and only produces errors on the specific cases you expect it to handle — such as missing lookup values or zero denominators. Only add IFERROR once you are confident in the inner formula.
Returns zero when a blank cell was needed
Setting value_if_error to 0 when a blank cell was intended causes zeros to appear in columns where they do not belong. This skews AVERAGE calculations, adds unwanted data points to charts, and may confuse readers who interpret zero as a genuine value rather than a missing one.
Fix: Use "" as value_if_error to return a visually blank cell. Reserve 0 only for situations where a numeric zero is genuinely the right representation of a missing or failed value.
Tips and variations
Use IFNA instead of IFERROR when handling only #N/A from lookup formulas
IFNA is the precise tool for lookup functions where #N/A means the value was not found. It catches only #N/A and lets all other errors — #REF!, #VALUE!, #NAME? — pass through and remain visible. This means genuine formula bugs in your VLOOKUP or XLOOKUP are not hidden behind a friendly message.
=IFNA(VLOOKUP(A2,Table,2,FALSE),"Not found")
Return a blank not zero for cleaner charts and averages
Blank cells are invisible to charts and are not counted in AVERAGE. A column of "" results when nothing is found looks clean, does not affect trend lines, and does not inflate or deflate calculated averages. Reserve zero for cases where zero is genuinely the correct numeric answer.
=IFERROR(formula,"")
Chain two lookups using IFERROR to create a fallback lookup path
When data exists in two different tables — a current table and an archive — try the primary table first. If that fails, IFERROR automatically runs the secondary lookup. Add a second IFERROR to show a message if both lookups fail.
=IFERROR(VLOOKUP(A2,Current!A:B,2,FALSE),IFERROR(VLOOKUP(A2,Archive!A:B,2,FALSE),"Not found"))
Excel vs Google Sheets
Excel vs Google Sheets
IFERROR works identically in Excel 2007+ and all versions of Google Sheets. Same syntax, same error types caught. For Excel 2003, use IF(ISERROR()) instead.
Frequently asked questions
IFERROR evaluates a formula or expression and checks whether the result is an error. If no error occurs, IFERROR returns the result unchanged. If any error occurs — including #N/A, #VALUE!, #REF!, #DIV/0!, #NAME?, #NUM!, or #NULL! — IFERROR returns the alternative value you specify in the second argument instead of showing the error in the cell. It is the standard way to make spreadsheets look clean and professional when some rows are expected to produce errors.
IFERROR catches every error type without distinction. IFNA only catches #N/A errors and passes all other error types through unchanged. For VLOOKUP and XLOOKUP formulas where #N/A specifically means the lookup value was not found, IFNA is the more precise and safer choice because a genuine #REF! or #VALUE! error in your formula will still be visible. IFERROR would hide those formula bugs silently. Use IFERROR when you genuinely want to suppress all error types regardless of cause.
Yes — use two double-quotes with nothing between them as the value_if_error argument: =IFERROR(formula,""). This makes the cell appear visually empty when the formula produces an error. Blank cells are preferable to zero in most reporting contexts because they do not appear as data points in charts, do not skew AVERAGE calculations, and signal clearly to readers that the value is absent rather than genuinely zero.
Yes — this is the primary risk and the most important thing to understand about IFERROR. Because it catches all error types, a genuine formula bug that produces #REF!, #VALUE!, or any other error will be silently replaced by your fallback value. The formula will appear to work but will return wrong results without any visible warning. Always test the inner formula without IFERROR first to confirm it works correctly and only errors on the cases you expect.
IFERROR was introduced in Excel 2007. It is not available in Excel 2003 or earlier. For Excel 2003, use the equivalent older pattern: =IF(ISERROR(formula),value_if_error,formula). This evaluates the formula twice — once inside ISERROR and once for the result — which is slightly less efficient but functionally identical. In Google Sheets, IFERROR is available in all versions.