IFERROR in Excel: catch errors without hiding problems

Syntax and how it evaluates

=IFERROR(value, value_if_error) has two arguments. Excel calculates value first. If the result is a normal number, text or date, IFERROR returns it unchanged. If it is an error, IFERROR returns value_if_error instead, which can be a number, text, an empty string "" or another formula. IFERROR exists since Excel 2007 and works the same in Google Sheets.

Two everyday patterns: safe division, =IFERROR(C2/D2,""), and a lookup with a message, =IFERROR(VLOOKUP(B2,$A$11:$B$13,2,FALSE),"Missing"). Lock the lookup table with $ before you fill the formula down, otherwise the table range moves and the lower rows start returning errors that IFERROR then hides.

Step by step through the example

Column E divides revenue by orders without protection. Station Rd has 0 orders and returns #DIV/0!, Airport has the text "pending" in the orders column and returns #VALUE!. =SUMPRODUCT(--ISERROR(E2:E7)) counts both, and ERROR.TYPE tells them apart (2 and 3). Column F wraps the same division in IFERROR with "" as fallback, so both cells look empty.

Because AVERAGE skips text, including the empty string, =AVERAGE(F2:F7) averages only the four valid stores: (80 + 65 + 70 + 90) ÷ 4 = 76.25. Replace the fallback with 0 and the two problem stores count as stores with an average order of zero: =AVERAGE(IFERROR(E2:E7,0)) drops to 50.83, a third lower, although no customer spent less. Choose the fallback by what the number means: blank or text for "no result", 0 only when zero is the true value.

IFERROR or IFNA?

IFNA(value, value_if_na), available since Excel 2013, works like IFERROR but only catches #N/A, the error that lookups return when a value is not found. In column G, region W is not in the commission table, so IFNA shows "Missing". If the formula itself were broken, for example a mistyped function name (#NAME?) or a deleted table (#REF!), IFNA would let that error through and you would notice it. IFERROR would turn it into "Missing" as well, and a broken lookup would look like a data gap. Rule of thumb: IFNA around lookups, IFERROR around calculations where you have checked which errors can occur. The #N/A guide explains why lookups fail in the first place.

Worked example: store figures with errors caught and counted

Six stores (yellow inputs). Column E shows the raw division with its errors, F and G the caught versions, and the block below compares the ways of dealing with them.

Worked example: store figures with errors caught and counted

	A	B	C	D	E	F	G
1	Store	Region	Revenue	Orders	Avg order (raw)	Avg order (IFERROR)	Rate (IFNA)
2	Harbour St	N	18,400	230	80.00	80.00	4.0%
3	Mill Lane	S	9,750	150	65.00	65.00	3.5%
4	Station Rd	E	0	0	#DIV/0!		5.0%
5	Riverside	W	12,600	180	70.00	70.00	Missing
6	Old Town	N	22,050	245	90.00	90.00	4.0%
7	Airport	S	7,200	pending	#VALUE!		3.5%
8
9	Commission table			New regions
10	Region	Rate		Region	Rate
11	N	4.0%		W	4.5%
12	S	3.5%		X	3.0%
13	E	5.0%
14
15	Check						Result
16	Stores where the raw formula returns an error						2
17	Error type of Station Rd (2 = #DIV/0!) and Airport (3 = #VALUE!)					2	3
18	Average order, errors left out (AVERAGE of column F)						76.25
19	Average order, errors left out (AGGREGATE on raw column E)						76.25
20	Average order if errors are replaced by 0 (misleading)						50.83
21	Rate for W: main table first, then new regions (nested IFERROR)						4.5%
22	Station Rd with an IF test instead of IFERROR						No orders
23	Commission on stores with a known rate						2,211.25

Fictitious stores and rates. Error codes as returned by ERROR.TYPE: 1 #NULL!, 2 #DIV/0!, 3 #VALUE!, 4 #REF!, 5 #NAME?, 6 #NUM!, 7 #N/A.

Nested IFERROR: a chain of fallbacks

Because value_if_error can be another formula, IFERROR can try a second source when the first fails. =IFERROR(VLOOKUP("W",A11:B13,2,FALSE),IFERROR(VLOOKUP("W",D11:E12,2,FALSE),"Missing")) searches the main commission table, then the table of new regions, and only then gives up. Region W is found in the second table at 4.5%. In Microsoft 365 the same chain is often shorter with XLOOKUP, whose fourth argument is a built-in not-found value.

Alternatives: IF tests and AGGREGATE

An explicit test documents why a result is missing. =IF(D4=0,"No orders",C4/D4) handles the zero case and nothing else: if D4 contains text, the error still appears, which is what you want while the data is unchecked. ISNUMBER, ISBLANK and ISERROR can build similar tests.

When you only need a total or an average over a column that contains errors, you do not have to change the column at all. =AGGREGATE(1,6,E2:E7) calculates AVERAGE (function 1) while ignoring error values (option 6) and also returns 76.25; function 9 gives SUM. SUMPRODUCT is another tolerant option: it treats text such as "Missing" as 0 when the arrays are passed as separate arguments, so =SUMPRODUCT(C2:C7,G2:G7) adds up the commission of the stores with a known rate, 2,211.25.

When not to use IFERROR

Do not wrap whole models in IFERROR "to make them look clean". Errors are signals: #REF! means a reference was deleted, #VALUE! means text sits where a number should be. Hiding them turns a visible problem into a wrong total. Add IFERROR at the last step, only to the cells where an error has a known, harmless cause, and keep a check cell such as the ISERROR count above so you notice when new errors appear.

Common mistakes

• Replacing errors with 0 by default. Zeros enter averages, minimums and counts. In the example the average order falls from 76.25 to 50.83.
• Hiding broken formulas. IFERROR also catches #REF! and #NAME?, so a deleted range or a typo looks like a missing value. Use IFNA around lookups.
• Unlocked lookup ranges. Filled down without $, the table range shifts, lower rows fail, and IFERROR shows the fallback instead of the real value.
• Wrapping the wrong part. =IFERROR(C2,"")/D2 still divides by zero. The division must be inside IFERROR.
• Using "" where a number is expected later. An empty string is text, so =F4*2 returns #VALUE!. SUM and AVERAGE skip it, plain arithmetic does not.