Understand all what IFERROR is a function in Excel, Google Sheets

IFFERROR is a rather special function, not used much in Excel and Google Sheets. For many of you, you may not know yet What is IFERROR function?, how to use as well as combine with other functions. So to understand better, let’s GhienCongListen Please refer to the following article.

Advertisement

What is IFERROR function?

Jaw IFERROR in Excel is designed to leverage and manage errors in formulas and calculations. More specific, IFERROR checks a formula and, if it returns an error, returns another value that you specify; otherwise, returns the result of the formula.

Syntax of the function IFERROR in Excel as follows:

Advertisement

=IFERROR(value, value_if_error)

Where:

Advertisement

• Value (required) – what to check for errors. It can be a formula, expression, value, or cell reference.
• Value_if_error (required) – the value to return if an error is found. It can be an empty string (blank cell), text message, numeric value, a formula or other calculation.

For example, when you split two columns of numbers, you might get a different set of errors if one of the columns contains blank cells, zeros, or text.

To prevent that from happening, use the . function IFERROR to catch and handle errors however you want.

If error, then leave blank. Then provide an empty string (“) for the value_if_error argument to return an empty cell if an error is found:

=IFERROR(A2/B2, “”)

If it fails, then you want to be able to display your own message instead of Excel’s standard error notation:

=IFERROR(A2/B2, “Error in calculation”)

5 things you should know about the IFERROR . function

• Jaw IFERROR in Excel handles all kinds of errors including #DIV/0!, #N/A, #NAME?, #NULL!, #NUM!, #REF!, and #VALUE!.
• Depending on the content of the value_if_error argument, IFERROR can replace the error with your custom text message, number, date or logical value, the result of another formula, or an empty string (blank cell).
• If the value argument is an empty cell, it is treated as an empty string (” “) but not an error.
• IFERROR was introduced in Excel 2007 and is available in all subsequent versions of Excel 2010, Excel 2013, and Excel 2016.

” Learn about: What is Null? Null in programming is different from error in Excel?

Example application of the IFERROR . function

The following examples show how to use the . function IFERROR in Excel and Google Sheets combined with other functions to accomplish more complex tasks.

IFERROR with VLOOKUP

One of the most common uses of the . function IFERROR is to let the user know that the value they are looking for does not exist in the dataset. For this you put a formula VLOOKUP in IFERROR like this:

IFERROR(VLOOKUP(…),”Not found”)

IFERROR (VLOOKUP ( … ), “Not found”)

If the lookup value is not in the table you are looking for, the formula VLOOKUP would normally return the #N/A error:

For your users, wrap the function VLOOKUP in IFERROR and display a more informative and user-friendly message:

=IFERROR(VLOOKUP(A2, ‘Lookup table’!$A$2:$B$4, 2,FALSE), “Not found”)

The screenshot below shows the recipe IFERROR this in Excel:

If you only want to make #N/A errors but not all errors, use the . function IFNA instead of IFERROR.

Nested IFERROR functions to perform sequential VLOOKUPs

In situations when you need to do a lot VLOOKUP based on VLOOKUP before success or failure, you can nest two or more functions IFERROR into another function.

Let’s say you have some sales reports from your company’s regional branches and you want to get the amount for a certain order ID. With A2 being the lookup value in the current sheet and A2:B5 being the lookup range in the 3 lookup worksheets (Report 1, Report 2 and Report 3), the formula is as follows:

=IFERROR(VLOOKUP(A2,’Report 1′!A2:B5,2,0),IFERROR(VLOOKUP(A2,’Report 2′!A2:B5,2,0),IFERROR(VLOOKUP(A2,’Report 3) ′!A2:B5,2,0),”not found”)))

The result will look like this:

IFERROR in array formula

As you probably know, array formula in Excel means doing multiple calculations in a single formula. If you provide an array formula or expression that results in an array in the function’s value argument IFERROR, it returns an array of values ​​for each cell in the specified range. The example below shows the details.

Say, you have Total in column B and Price in column C and you want to calculate Total Quantity. This can be done by using the following array formula, which divides each cell in the range B2:B4 by the corresponding cell in the range C2:C4, and then adds the results:

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

The formula works fine as long as the divisor range has no zeros or empty cells. If there is at least one value 0 or empty cell, the value #DIV/0! error returned:

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

=SUM(IFERROR($B$2:$B$4/$C$2:$C$4,0))

What the formula does is divide a value in column B by a value in column C in each row (100/2, 200/5 and 0/0) and return the resulting array {50; 40; # DIV / 0!}. Jaw IFERROR catch all #DIV/0! errors and replace them with zeros. And then the SUM function adds the values ​​in the resulting array {50; 40; 0} and output the final result (50 + 40 = 90).

Note: Remember that array formulas must be completed by pressing Ctrl + Shift + Enter – shortcut.

IFERROR vs. IF ISERROR

Now you know the use of the function IFERROR how easy it is in Excel, you might wonder why some people still lean towards using a combination IFERROR. What advantage does it have over IFERROR? Nobody. In the olden days of Excel 2003 and below when IFERROR does not exist, IF ISERROR is the only possible way to trap the error. In Excel 2007 and later, it’s a bit more complicated to achieve the same result.

For example, to catch VLOOKUP errors, you can use one of the formulas below.

In Excel 2007 – Excel 2016:

IFERROR(VLOOKUP(…), “Not found”)

In all versions of Excel:

IF(ISERROR(VLOOKUP(…)), “Not found”, VLOOKUP(…))

Note that in the formula VLOOKUP IF ISERRORyou have to VLOOKUP twice. In plain English, the formula can be read as follows: If the result VLOOKUP error, return “Not found”, otherwise output the result VLOOKUP.

And here is a real example of an Excel formula If Iserror VLOOKUP:

=IF(ISERROR(VLOOKUP(D2, A2:B5,2,FALSE)),”Not found”, VLOOKUP(D2, A2:B5,2,FALSE ))

IFERROR vs. IFNA

Introduced with Excel 2013, IFNA is another function to check formula errors. Its syntax is similar to IFERROR:

IFNA(value, value_if_na)

• IFNA other IFERROR at what point? – Jaw IFNA only catch #N/A error while function IFERROR handle all kinds of errors.
• You may want to use IFNA In what cases? – For example, when working with important or sensitive data, you may want to be alerted to possible errors in your data set, and standard Excel error messages with the “#” symbol may can be vivid visual indicators.

See how you can create a formula that displays the message “Not found” instead of the #N/A error, which appears when the lookup value is not in the data set, but brings you to other Excel errors .

Let’s say you want to pull Qty. from the lookup table to the summary table as shown in the screenshot below. Use the formula IFERROR VLOOKUP in Excel will produce aesthetically pleasing results, which is not technically correct because Lemons exists in the lookup table:

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

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

Or, combine IF ISNA in Excel 2010 and earlier versions:

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

Formula syntax IFNA VLOOKUP and IF ISNA VLOOKUP same as the formula IFERROR VLOOKUP and IF ISERROR VLOOKUP discussed earlier.

As shown in the screenshot below, the formula Ifna VLOOKUP returns “Not found” only for entries not in the lookup table (Peaches). For Lemons , it shows #DIV/0! indicates that our lookup table contains a divide-by-zero error:

Best practices for using the IFERROR . function

Now you know that the function IFERROR is the easiest way to catch errors in Excel and mask them with blank cells, zero values ​​or your own custom message. However, that doesn’t mean you should end each formula with error handling. The following simple suggestions can help you stay in balance.

• Don’t make mistakes for no reason.
• Wrap the smallest possible portion of a recipe in IFERROR.
• To handle only specific errors, use a smaller scope error handling function:
  – IFNA or IF ISNA to catch the #N/A error only.
  – ISERR to catch all errors except #N/A errors.

See more:

Above, TechtipsNReview.com has answered for you what IFERROR is. If you have any questions, please comment below. Don’t forget to Like and Share if you find this article useful and it is also the motivation for TechtipsNReview.com to bring more useful content in the future.

Refer to Ablebits