IFERROR Explained: Replace Formula Errors, Not Hide Them

Two required arguments in Excel, one optional in Sheets

Excel's syntax is IFERROR(value, value_if_error), and both arguments are required. Microsoft lists it for Excel for Microsoft 365, Excel 2024, Excel 2021, Excel 2019, Excel 2016 and the Excel Web App.

Google Sheets writes it as IFERROR(value, [value_if_error]). There the second argument is optional and blank by default, so an error with no second argument gives an empty cell. In Excel you must type the second argument, even if it is "".

The 7 errors it catches

Microsoft lists the full set: #N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME? and #NULL!. IFERROR cannot tell them apart. The same replacement appears for a missing lookup value (#N/A), a typo in a function name (#NAME?) and a deleted column (#REF!).

That is why it can hide real faults. A broken reference and an expected empty result look identical once the cell says "Not found".

A worked example: three rows of division

Microsoft's example has A2:B4 holding 210 and 35, 55 and 0, and a blank and 23. The formula is =IFERROR(A2/B2,"Error in calculation") filled down 3 rows.

Row 2: 210 / 35 is 6, no error, so the cell shows 6. Row 3: 55 / 0 is a #DIV/0! error, so the cell shows Error in calculation. Row 4: blank / 23 is 0 with no error, so the cell shows 0.

Row 4 matters. A blank is not an error, so IFERROR leaves the 0 alone. Also, an empty cell passed as value or value_if_error is treated as an empty string.

Handling an error means fixing its cause first

Microsoft's #N/A page lists what usually causes the error before it suggests IFERROR. The causes are a lookup value missing from the source, a number compared with text, leading or trailing spaces, and an approximate match in VLOOKUP or HLOOKUP. It also says to use FALSE for exact match in VLOOKUP and to set match_type to 0 in MATCH.

Only then does it suggest wrapping the formula, as in =IFERROR(FORMULA(),0), or returning "" or custom text. So the order is: find out why, fix what can be fixed, then wrap what is genuinely expected, such as a customer who is not in the list yet.

IFNA replaces #N/A and nothing else

IFNA(value, value_if_na) has 2 required arguments and handles one error. Microsoft's example wraps a VLOOKUP for Seattle: when Seattle is not in the range the result is #N/A and IFNA shows Not found. A real #REF! or #NAME? still shows, which is what you want, since it tells you the formula is broken.

Microsoft lists IFNA for Excel 365, 2024, 2021, 2019 and 2016, but not for Excel 2013, so the page does not confirm support there. Google Sheets also has IFNA, plus ISNA, ISERR and ISERROR for tests inside IF.

When an error is the correct answer

Some errors carry information. Microsoft notes that #N/A values do not plot on charts, so they can be used on purpose to leave a gap in a line. Replacing them with 0 gives the chart a real number to plot instead.

Empty FILTER results are another case. Microsoft's #CALC! page fixes an empty array with the if_empty argument, as in =FILTER(C3:D5,D3:D5<100,0), which is cleaner than wrapping the whole formula in IFERROR.

What VLOOKUP's two errors tell you before you wrap it

Microsoft's VLOOKUP page separates the errors. #N/A means an exact match found nothing, or an approximate lookup value is smaller than the smallest value in the first column. #REF! means col_index_num is larger than the number of columns in table_array. #NAME? usually points to missing quotes around text.

Take =VLOOKUP("Seattle",A2:C10,5,FALSE). The table has 3 columns, so 5 returns #REF!, and =IFERROR(VLOOKUP("Seattle",A2:C10,5,FALSE),"Not found") would say Not found for a typing mistake. Change 5 to 3 first. Then Seattle either returns a value, or returns #N/A for a real absence, and IFNA(...,"Not found") is the right wrapper.

Nesting is allowed, up to 64 levels of functions in Excel, but 2 or 3 nested IFERRORs are hard to read. A chain such as IFERROR(lookup1, IFERROR(lookup2, "none")) is acceptable for 2 sources; for more, restructure the data.

Array formulas and Google Sheets differences

If value is an array formula, Excel returns an array with one result per cell. In Microsoft 365, press Enter in the top-left cell. In older versions, select the output range and press Ctrl+Shift+Enter.

Google says IFERROR(exp1,exp2) is logically equivalent to IF(NOT(ISERROR(exp1)),exp1,exp2). Use that form if you ever need to test for one error type with ISNA or ISERR.

Google's own examples show 2 typical uses. One returns 0 for a unit price when Quantity is empty, and the other returns a custom message when a Student ID is not found in a grades table. The first is a planned gap in the data, the second a lookup that can legitimately miss, which are the 2 cases where a wrapper is justified.

Who should use it, and who should not

It suits report cells where a gap is expected: a ratio with a zero denominator, or a lookup against a list that grows. It does not suit a formula you are still building, because it can hide the message that shows what is wrong. Build the formula first, then add IFERROR as the last step.

If you are looking at an error message right now and want it explained, Crade can read the screen and tell you which of the 7 types it is.