IFERROR checks a value or formula and returns a fallback you choose if it produces any error. If there is no error, it simply returns the original result.
=IFERROR(value, value_if_error)What does IFERROR do?
IFERROR is a logical function that wraps another value or formula and catches any error it produces. You give it two things: the value or calculation to test, and the result to show if that test fails. When the test succeeds, IFERROR passes the original result straight through. When it fails, it swaps in your fallback instead of showing an ugly error code.
Errors like #DIV/0!, #VALUE!, #N/A, and #REF! are useful while you are building a sheet, but they look unprofessional in a finished report and they break totals, charts, and lookups. IFERROR lets you keep the logic intact while presenting a clean result such as a zero, a blank, or a friendly message. It also saves you from writing long IF formulas that repeat the same calculation twice.
A common situation: you build a sales report with a column that divides revenue by units sold. Some rows have zero units, so Excel shows #DIV/0! and the grand total fails. Wrapping the division in IFERROR lets you show 0 for those rows, keep the total working, and hand the report to your manager without apologising for red error cells.
When to use IFERROR
- Hiding #DIV/0! in average, ratio, or percentage columns when the divisor can be zero.
- Returning a blank or a message when VLOOKUP or XLOOKUP cannot find a match.
- Keeping totals and charts working when some source cells contain errors.
- Showing a friendly note instead of #VALUE! when users type text into number fields.
- Cleaning imported data where some cells are missing or malformed.
- Protecting dashboards and reports that are shared with people outside your team.
IFERROR syntax
=IFERROR(value, value_if_error)| Argument | Required? | What to enter |
|---|---|---|
value | Required | The value, cell reference, or formula to check. If it produces any error, IFERROR returns the fallback instead. |
value_if_error | Required | What to return when value produces an error. It can be a number, text in quotes, a blank "", or another formula. |
What it returns: The original result of value when no error occurs, or value_if_error when any error occurs. The return type depends on what you supply: a number, text, a blank, or the result of another formula.
IFERROR examples
1 Replace a division error with zero
The goal is to calculate revenue per unit without showing #DIV/0! when units are zero. The formula =IFERROR(B2/C2, 0) divides sales by units for the first row. Because C2 is 10, the division succeeds and returns 120. IFERROR passes that result through unchanged. If C2 had been 0, the formula would return 0 instead of an error.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Product | Sales | Units | |||
| 2 | Widget | 1200 | 10 | 120 | ||
| 3 | Gadget | 900 | 0 | |||
| 4 | Gizmo | 1500 | 25 | |||
| 5 |
2 Show a blank when a lookup finds nothing
Here you want to pull a price for an item code, but some codes are not in the price list. The formula =IFERROR(VLOOKUP(A2, D2:E4, 2, FALSE), "") looks up the code in A2 inside the table D2:E4. If the code is found, it returns the price from column E. If the code is missing, VLOOKUP produces #N/A and IFERROR returns an empty string, so the cell looks blank rather than showing an error.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Item Code | Quantity | Code | Price | |||
| 2 | P-100 | 3 | P-100 | 12.5 | 12.5 | ||
| 3 | P-200 | 1 | P-200 | 8 | |||
| 4 | P-999 | 2 | P-300 | 15 | |||
| 5 |
3 Return a message when text is used in a calculation
The goal is to multiply quantity by unit price, but one row contains the word pending instead of a number. The formula =IFERROR(B2*C2, "Check input") multiplies the two cells. For row 2, 4 times 25 works and returns 100. IFERROR passes the number through. If a cell contained text, the multiplication would produce #VALUE! and the formula would show Check input instead.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Order | Quantity | Unit Price | |||
| 2 | SO-01 | 4 | 25 | 100 | ||
| 3 | SO-02 | pending | 10 | |||
| 4 | SO-03 | 2 | 15 | |||
| 5 |
4 Keep a percentage column clean when the base is zero
You want to show each region's share of total sales as a percentage. The formula =IFERROR(B2/$B$5, 0) divides the region's sales by the total in B5. For North, 1200 divided by 3000 equals 0.4, so IFERROR returns 0.4. If the total were zero, the division would fail and the formula would return 0 instead of #DIV/0!, keeping the column tidy for a chart.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Region | Sales | |||
| 2 | North | 1200 | 0.4 | ||
| 3 | South | 900 | |||
| 4 | East | 900 | |||
| 5 | Total | 3000 | |||
| 6 |
5 Handle a missing date in a days-open calculation
The goal is to calculate how many days have passed since an invoice was issued, but one invoice has no issue date. The formula =IFERROR(DATE(2024,6,30)-B2, "No date") subtracts the issue date in B2 from 30 June 2024. For the first invoice, the result is 30 days. If B2 were blank or text, the subtraction would produce an error and IFERROR would return No date, so the ageing report stays readable.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Invoice | Issue Date | |||
| 2 | INV-001 | 2024-05-31 | 30 | ||
| 3 | INV-002 | 2024-06-10 | |||
| 4 | INV-003 | ||||
| 5 |
Common IFERROR errors and how to fix them
| Error | Why it happens | How to fix it |
|---|---|---|
| #NAME? | The function name is misspelled, for example IFFERROR or IFEROR. | Type =IFERROR( and let Excel's formula suggestions complete the name, or correct the spelling manually. |
| #VALUE! | The value_if_error argument is text that is not wrapped in double quotes, so Excel tries to interpret it as a formula or name. | Put text fallbacks in quotes, such as "Not found", or use a number like 0 without quotes. |
| #N/A | The error occurred outside the IFERROR wrapper, for example in a cell that IFERROR refers to but does not itself calculate. | Wrap the formula that actually produces the error, not just the cell that displays it, or trace the source cell and fix the lookup. |
| Formula shows a blank when you expected | The value_if_error is an empty string "", and the tested formula is failing silently. | Temporarily change the fallback to a visible marker such as "ERROR" to confirm whether the main formula is failing, then fix the root cause. |
| #REF! | The value argument refers to a deleted column or sheet, so IFERROR cannot evaluate it. | Update the reference to point to an existing range, or use the Undo command immediately after deleting to restore the reference. |
Tips for using IFERROR
- Use IFERROR to hide errors in finished reports, but remove it temporarily while debugging so you can see what is actually failing.
- Prefer a meaningful fallback such as 0 for numbers or "Not found" for lookups rather than an empty string, so readers know the result is intentional.
- IFERROR catches every error type, including #N/A from lookups; if you only want to handle missing lookups, IFNA is more precise.
- Wrap the whole calculation once rather than wrapping each part, which keeps the formula shorter and easier to audit.
- Be careful with blank fallbacks in numeric columns: a blank is text, so later SUM or AVERAGE formulas may ignore or misread it.
- Use IFERROR around imported or user-entered data to keep dashboards stable when source values change.
Frequently asked questions
What is the difference between IFERROR and IFNA?
IFERROR catches every error type, including #DIV/0!, #VALUE!, and #REF!. IFNA catches only #N/A, which is the error a lookup returns when no match is found. Use IFNA when you want other errors to remain visible.
Does IFERROR hide real problems in my spreadsheet?
It can if you use it everywhere without checking. The safest approach is to build and test the formula first, confirm it works, then add IFERROR for presentation. If a report suddenly shows many fallback values, remove IFERROR temporarily to see the underlying errors.
Can IFERROR return a blank cell?
Yes. Use an empty string as the second argument, for example =IFERROR(A2/B2, ""). The cell looks blank, but it contains an empty text string, which matters for functions that count or sum values.
Is IFERROR available in older versions of Excel?
IFERROR was introduced in Excel 2007 and is available in Excel 2010 and later, Excel for Microsoft 365, Excel for the web, and Excel for Mac 2011 and later. Very old versions require a combination of IF and ISERROR instead.
Can I use IFERROR with VLOOKUP?
Yes, it is one of the most common combinations. Wrap the VLOOKUP in IFERROR to return a friendly message or blank when the lookup value is not found, for example =IFERROR(VLOOKUP(A2, D2:E10, 2, FALSE), "Not found").
Related functions
Every example on this page was recalculated by SheetSkill’s spreadsheet engine. IFERROR reference