How to use IFERROR in Excel with sample formulas (2023)

The tutorial shows you how to use IFERROR in Excel to detect errors and replace them with a blank cell, a different value, or a custom message. You'll learn how to use IFERROR with Vlookup and Index Match and how it relates to IF ISERROR and IFNA.

"Give me a foothold and I'll move the earth," Archimedes once said. "Give me a formula and I'll make it return an error," an Excel user would say. In this tutorial, we're not going to look at how to return errors in Excel, but rather learn how to return errorshow to avoid them to keep spreadsheets clean and formulas clear.

Excel function IFERROR - syntax and basic usage

The IF function in Excel is used to detect and handle errors in formulas and calculations. More specifically, IFERROR checks a formula and if it returns an error, it returns another specified value. Otherwise, the result of the formula is returned.

The syntax for the Excel IFERROR function is as follows:

IF(value, value_if_error)

Where:

value(required) - what to check for errors. This can be a formula, expression, value, or cell reference.
Value_if_error(required) - what to return when an error is encountered. It can be an empty string (empty cell), text message, numerical value, other formula or calculation.

For example, when dividing two columns of numbers, you can get many different errors if one of the columns contains blank cells, zeros, or text.

To avoid this, use the IFERROR function to find and handle errors as you see fit.

If there is an error, it's blank

Pass an empty string ("") tovalue_if_errorargument returning a blank cell if an error occurs:

= ferr(aa/ba, „”)

If an error occurs, a message will be displayed

You can also display your own message instead of the standard Excel error format:

= sorry (aa/ba, "breaking up")

5 things you should know about the Excel IF function

The IF function in Excel handles all kinds of errors, including #DIV/0!, #N/A, #NAME?, #NULL!, #NUM!, #REF!and #VALUE!.
Depending on the contentvalue_if_errorThe IFERROR argument can replace errors with a custom text message, a number, date or boolean value, a different type of result, or an empty string (blank cell).
asvalueThe argument is a blank cell and is treated as an empty string (""), but not as an error.
IFERROR was introduced in Excel 2007 and is available in all subsequent versions of Excel 2010, Excel 2013, Excel 2016, Excel 2019, Excel 2021, and Excel 365.
To debug Excel 2003 and earlier versions, use the IERROR function in conjunction with the IF as shown inthis example.

IFERROR type examples

The following examples show how you can use IFERROR in Excel in conjunction with other functions to perform more complex tasks.

Excel IFERROR με Vlookup

One of the most common uses of the IFERROR function is to tell the user that the value they are looking for does not exist in the dataset.REQUESTStype IFERROR as follows:

Nested IFERROR functions for sequential browsing in Excel

In cases where you need to run multiple Vlookups depending on whether the previous Vlookups were successful or failed, you can nest two or more IFERROR functions together.

Suppose you have multiple sales reports from your company's regional offices, and you want to get the amount for a specific order ID, using A2 as the search value in the current sheet and A2:B5 as the search range in the 3 search sheets (Report 1, Reference 2 and Reference 3), the pattern is as follows:

=IF VLOOKUP(A2,'Reference 1'!A2:B5,2,0),IF(VLOOKUP(A2,'Reference 2'!A2:B5,2,0),IF(VLOOKUP)(A2;'Reference 3 '!A2:B5,2,0),"not found")))

The result looks like this:

For a detailed explanation of the logic of the formula, seeHow to perform sequential review in Excel.

IFERROR to the array type

As you may knowarray typein Excel are used to perform multiple calculations in a single formula. If you specify an array type or array expression invalueAs an argument to the IF function, this would return an array of values for each cell in the specified range. The following example shows the details.

Let's say yesTotalin column B iCenain column C and you want to calculatetotal amountYou can do this using the followingtable type,dividing each cell in the range B2:B4 by the corresponding cell in the range C2:C4, then adding the results:

=SOM($B$2:$B$4/$C$2:$C$4)

The formula works fine as long as the divider range does not contain zero or blank cells. If there is at least one 0 or blank cell, #DIV/0!

To fix this error, just do division in the IFERROR function:

=SOM(IF($B$2:$B$4/$C$2:$C$4.0))

The formula is to divide the value in column B by the value in column C in each row (100/2, 200/5 and 0/0) and return the result string {50;40;#DIV/0 !} The IFERROR function catches all errors #DIVIDE/0!and replaces them with zeros. The SUM function then sums the values in the resulting array {50;40;0} and returns the final result (50+40=90).

Attention.Please note that the table types must be filled in by clicking on themCtrl+Shift+Entershortest way.

Oops Contra Alice Oops

Now that you know how easy it is to use the IF function in Excel, you might wonder why some people still prefer the IF combination. Does it have any advantage over ALS IFERROR? NO. Back in the bad old days of Excel 2003 and olderversion, when there was no IFERROR, the only possible method of debugging was IF IERROR. In Excel 2007 and later, this is a slightly more complicated way to achieve the same result.

For example, to debug Vlookup, you can use one of the following formulas.

In Excel 2007 - Excel 2016:

IF ERROR(VLOOKUP(…), "Not found")

In all versions of Excel:

IF(ISERROR(VLOOKUP(...)), "Not Found", VLOOKUP(...))

Note that in the formula IF IERROR VLOOKUP, you must use the VLOOKUP function twice. In plain English, the formula can be read as follows: If the vertical search fails, return "Not Found", otherwise print the vertical search result.

And here is a real example of the Excel If Iserror Vlookup formula:

=IF(ISERROR(VLOOKUP(D2,A2:B5,2,FALSE)),"Not Found", VLOOKUP(D2,A2:B5,2,FALSE ))

For more information, seeUsing the IS ERROR function in Excel.

IFERROR against IFNA

Introduced in Excel 2013, the IFNA function is another function for checking a formula for errors. The syntax is similar to the IFERROR syntax:

IFNA(value; value_if_if)

What is the difference between IFNA and IFERROR? The IFNA feature is catching up#N/A errors onlywhile IFERROR handles all error types.

In what situations would you like to use IFNA? When it is not wise to mask all errors. For example, if you are working with important or sensitive data, you may want to be notified of potential errors in the dataset and its common error messages. Excel with "#" symbolcan be a living visual signal.

Let's see how to create a formula that displays "Not Found" instead of the N/A error that occurs when the value you are looking for does not exist in the dataset but highlights other Excel errors.

Suppose you want to fetch a number from lookup table to summary table as below screenshot shown.Viewing Excel type errorswould give an aesthetic effect, which is technically incorrect becauselemon treeare in the lookup table:

To detect #N/A but display the #DIV/0 error, use the IFNA function in Excel 2013 and Excel 2016:

=IFNA(VLOOKUP(F3,$A$3:$D$6,4,FALSE), "Not found")

Or a combination of IF ISNA in Excel 2010 and earlier:

=IF(ISNA(VLOOKUP(F3,$A$3:$D$6,4,FALSE)),"Not Found", VLOOKUP(F3,$A$3:$D$6,4,FALSE))

The syntax of the IFNA VLOOKUP and IF ISNA VLOOKUP formulas is similar to that ofIF ERROR PLEASEWIF ERROR IS DISPLAYED INSTALLdiscussed earlier.

As shown in the screenshot below, the Ifna Vlookup formula returns "Not Found" only for an item that does not exist in the lookup table (peaches).Forlemon tree, shows #DIV/0!, indicating that the lookup table contains a division-by-zero error:

More details can be found inUsing the IFNA function in Excel.

Best practices for using IFERROR in Excel

You already know that the IF function is the easiest way to find errors in Excel and cover them with blank cells, null values, or your own custom messages. But that doesn't mean that you need to include error handling in every formula.The following simple recommendations will help you stay in balance.

Don't notice mistakes for no reason.
Pack the smallest possible part of the formula into IFERROR.
If you only want to handle specific errors, use a narrower error handling function:
- IFNA or IF ISNA to catch #N/A errors only.
- ISERR to catch all errors except #N/A.

This is how you use the IF function in Excel to find and handle errors. For a closer look at the formulas discussed in this tutorial, download our sampleIFERROR Excel WorkbookThanks for reading and hope to see you on our blog next week.