IFERROR Function in Excel, Google Sheets, Airtable & Notion
IFERROR returns the result of a formula, or a fallback value if that formula produces an error such as #DIV/0! or #N/A.
Syntax in each app
| App | Support | Syntax & notes |
|---|---|---|
| Excel | ✔ Built in | IFERROR(value, value_if_error) |
| Google Sheets | ✔ Built in | IFERROR(value, [value_if_error])If value_if_error is omitted, errors become an empty cell. |
| Airtable | ≈ Workaround | IF(ISERROR(value), value_if_error, value)Airtable has no IFERROR. Wrap the expression in ISERROR, or test the risky input directly; for a division, the copy button checks the denominator first. |
| Notion | ≈ Workaround | if(denominator == 0, fallback, numerator / denominator)Notion has no error-catching function. Guard the risky part with if() before it runs, e.g. check for zero or empty(). |
| Excel | =IFERROR(150 / 0, 0) | open excel.new ↗ |
|---|---|---|
| Google Sheets | =IFERROR(150 / 0, 0) | open sheets.new ↗ |
| Airtable workaround | IF(0 = 0, 0, 150 / 0) | |
| Notion workaround | if(0 == 0, 0, 150 / 0) |
Excel & Sheets use cell ranges. Paste this sample data into cell A1 of a blank sheet, then paste the formula into any empty cell.
With a denominator of 0, Excel would show #DIV/0!; IFERROR returns the fallback instead. In Notion, keep the fallback a number so both branches have the same type.
Arguments
| Argument | What it means |
|---|---|
value | The formula that might fail. |
value_if_error | What to return instead of the error: 0, "", a message, or another formula. |
Examples
| Formula | Result | Why | Copy |
|---|---|---|---|
=IFERROR(150/0, 0) | 0 | Division by zero is caught. | |
=IFERROR(150/4, 0) | 37.5 | No error, so the normal result is returned. | |
=IFERROR(VLOOKUP(A2, D:E, 2, FALSE), "Not found") | "Not found" if missing | Friendly message instead of #N/A. | |
=IFERROR(1/0, "") | (empty text) | Shows a blank-looking cell. |
Examples use Excel / Google Sheets syntax unless noted.
How IFERROR works
IFERROR catches every error type: #DIV/0!, #N/A, #VALUE!, #REF!, #NUM!, #NAME? and #NULL!. That is convenient for presentation, but it can also hide real mistakes such as a misspelt function name or a deleted column. When you only expect a lookup to miss, IFNA is safer because it catches #N/A and lets other errors through.
Another trap is replacing errors with 0 in figures that are later averaged: a zero pulls an AVERAGE down, while an empty string "" is ignored by it. Choose the fallback with the next calculation in mind.
Airtable has ISERROR() but no IFERROR, so write IF(ISERROR(expr), fallback, expr), or test the input that causes the problem, as the copy button does: IF({Units} = 0, 0, {Revenue} / {Units}). Notion has no way to catch an error after it happens, so prevent it instead: if(prop("Units") == 0, 0, prop("Revenue") / prop("Units")), or check empty() for missing values.
Frequently Asked Questions
- What is the difference between IFERROR and IFNA?
- IFERROR catches all error types; IFNA catches only
#N/A, the error lookups return when nothing matches. IFNA does not hide other problems like a broken reference. - How do I show a blank instead of #DIV/0!?
- Use
=IFERROR(A2/B2, ""), or=IF(B2=0, "", A2/B2)to only handle the zero case.