IFERROR In Google Sheets

What Is IFERROR In Google Sheets?

The IFERROR in Google Sheets is a built-in Logical function. It accepts a value, such as a formula, to check for an error and the value to display if the input value evaluates to an error. So, the function returns the value specified as the second argument, if the first argument value evaluates to an error value. Otherwise, the function output is the return value of the formula cited as the first argument.

Users can make use of the IFERROR function in Google Sheets to identify errors in the entered formulas and display error-free and meaningful results.

Consider that we have a source dataset showing the monthly inventory costs and levels.

IFERROR in Google Sheets - Intro

We must determine the cost per carton in the range D2:D7 based on the input data. Additionally, if, for any month, the input data is unavailable, the corresponding target cell must show a meaningful comment instead of an error value.

Then, we can implement the IFERROR() in the target cells to acquire the results, in line with the meaning of IFERROR In Google Sheets explained previously.

IFERROR in Google Sheets - Intro - Output

The IFERROR() accepts two input values. The first argument is the formula to determine the cost per carton based on the source data. Next, the second argument is the text that should appear if the formula supplied as the first argument returns an error value.

Thus, for the specified inputs, the IFERROR in Google Sheets returns the required cost per carton value if the input values are valid. However, the function output is the text, which we supplied as the second argument when the specified division expression returns the #DIV/0! error.

Key Takeaways
  • The IFERROR in Google Sheets enables one to show a customized output (typically a text string) when the specified value (usually a formula) evaluates to an error.
  • The IFERROR function in Google Sheets is useful in identifying and handling errors such as the #DIV/0!, #VALUE!, #NUM!, #NAME?, and #N/A error.
  • The IFERROR function in Google Sheets accepts two compulsory arguments, value and value_if_error, as its input.
  • We can utilize the IFERROR function in Google Sheets as an individual function. However, applying the function with other inbuilt functions, such as VLOOKUP, makes it highly practical.

IFERROR() Google Sheets Formula 

The IFERROR function in Google Sheets syntax is the following:

IFERROR in Google Sheets - Formula

Where,

  • value: The value that we aim to check for an error.
  • value_if_error: The value the function must show if the formula, supplied as the first argument, evaluates to an error. The function can identify and handle the following error types: #DIV/0! error, #N/A, #NAME?, #NUM!, #REF!, and #VALUE! error.

We must provide both the argument values when using IFERROR in Google Sheets.

How To Use IFERROR In Google Sheets?

We can utilize the IFERROR function in Google Sheets in the following two ways:

  1. Access the function from the ribbon.
  2. Enter the function into the sheet manually.

Method #1 – Access The Function From The Ribbon

Choose a cell for showcasing the output value – The Insert tab – The Function option right arrow – The Logical function group right arrow – The IFERROR function.

IFERROR in Google Sheets - Method 1

The selected function appears in the chosen cell, with the cursor within the function brackets. We can now enter the IFERROR in Google Sheets arguments inside the brackets.

IFERROR in Google Sheets - Method 1 - sheet

Further, we can click the ‘?’ symbol against the function name to see its syntax.

IFERROR in Google Sheets - Method 1 - Syntax

Next, click the down arrow in the syntax window to see the meaning of the function IFERROR In Google Sheets explained with an illustration.

IFERROR in Google Sheets - Method 1 - Meaning

Finally, once we update the IFERROR in Google Sheets arguments, separated by commas, press Enter to secure the function output.

Method #2 – Enter The Function Into The Sheet Manually

  1. Click the cell where we want to display the result.
  2. Type =IFERROR( in the cell. [ Alternatively, type =I or =IF and click the function name IFERROR from the suggestions to select the function.]
  3. Supply the argument values, separated by commas, and close the brackets.
  4. Press Enter to secure the value the function IFERROR in Google Sheets returns.

IFERROR Examples

The following illustrations describe the practical ways of using IFERROR in Google Sheets.

#1 – “#N/A” Error

The source dataset contains two lists. While the first is the list of the ten biggest tech companies, the second is the list of the top ten US tech companies.

IFERROR in Google Sheets - NA Error

We need to check if each of the top ten US tech companies is in the first list and display the output in the range C2:C11. Otherwise, the corresponding target cell must show the text “No” instead of an error value.

Step 1: Choose cell C2, enter the VLOOKUP function, and press Enter.

=VLOOKUP(B2,$A$2:$A$11,1,0)

IFERROR in Google Sheets - NA Error - Step 1

Next, using the fill handle, feed the formula into the remaining target cells.

IFERROR in Google Sheets - NA Error - Step 1 - fill handle

The output shows that only the first two top US tech companies are on the first list. The remaining target cells show the #N/A error, indicating the other US tech companies are not on the first list.

However, the requirement is that the target cell must show the text “No” instead of the error value.

Then, we can overcome the issue by using the VLOOKUP() within the IFERROR() in each target cell. We shall use the range D2:D11 as the new target cell range.

Step 2: Choose cell D2, enter the following expression, and press Enter.

=IFERROR(VLOOKUP(B2,$A$2:$A$11,1,0),”No”)

IFERROR in Google Sheets - NA Error - Step 2

[Alternatively, select the target cell and then Insert Function Logical IFERROR function.

IFERROR in Google Sheets - NA Error - Step 1 - Alternative

This action will insert the chosen function in the specified cell, with the cursor within its brackets.

IFERROR in Google Sheets - NA Error - Step 1 - Alternative - function

Update the function argument values, separated with commas, as depicted below.

IFERROR in Google Sheets - NA Error - Step 2 - function argument

Finally, press Enter to view the function return value.]

Step 3: Utilize the fill handle to update the formula in the remaining target cells.

IFERROR in Google Sheets - NA Error - Step 3

Firstly, the VLOOKUP() searches for the lookup value in the cited column A cell range. If the function identifies a match, it returns the corresponding return value. Otherwise, its output is the #N/A error value. 

Next, the IFERROR() checks if the VLOOKUP() output is an error value. And, if it is so, then the IFERROR() returns the text “No”, supplied as the second argument. Otherwise, it returns the VLOOKUP() return value.

#2 – “#DIV/0!” Error

The source dataset contains a set of item codes, their order costs and units of ordered data.

IFERROR in Google Sheets - DIV Error

The task is to find the cost per unit of each item and show the outcome in the range D2:D6. Otherwise, the corresponding target cell must show a meaningful text instead of an error value.

Step 1: Select cell D2, enter the following expression, and press Enter.

=B2/C2

IFERROR in Google Sheets - Div Error - Step 1

Next, using the fill handle, we shall enter the formula in the remaining target cells.

Div Error - Step 1 - fill handle

The output shows that the formula output in the second and third target cells is the #DIV/0! Error since the denominator values in the two target cells’ formulas are 0.

However, the requirement is that the target cell must show a meaningful text instead of the error value.

Then, we can resolve the issue by applying the required formula within the IFERROR() in each target cell. We shall use the range E2:E6 as the new target cell range.

Step 2: Choose cell E2, enter the formula below, and press Enter.

=IFERROR(B2/C2,”Total units ordered data unavailable.”)

IFERROR in Google Sheets - Div Error - Step 2

Step 3: Use the fill handle to implement the formula in the remaining target cells.

IFERROR in Google Sheets - Div Error - Step 3

Firstly, the division formula determines the required cost per unit of each item. Next, the IFERROR() checks if the division formula output is an error value, in which case it returns the text specified as the function’s second argument value. Otherwise, the IFERROR() output is the division formula output.

#3 – “#NAME?” Error

We have a list of teams and their headcount data.

Name error

We must find the average headcount and display the value in cell B7. However, in the case of an error, the target cell must show a valid comment instead of the error value.

Step 1: Choose cell B7, enter the AVERAGE function, and press Enter.

=AVEREGE(B2:B6)

Name Error - Step 1

The function output is the #NAME? error, as in this case, the function name is incorrect. So, here is how to avoid the error from displaying in the target cell. 

We shall show the solution using the formula mentioned above within the IFERROR(). Let us assume the target cell is B8.

Step 2: Choose the target cell B8, enter the following formula, and press Enter.

=IFERROR(AVEREGE(B2:B6),”Check the formula syntax.”)

IFERROR in Google Sheets - Name Error - Step 2

Firstly, the AVERAGE() returns the #NAME? error, for the reason mentioned earlier. Next, the IFERROR() checks whether the specified formula output is an error value. Since, in this case, the formula output is an error value, the IFERROR() output is the text supplied as the second argument value.

#4 – “#NULL!” Error

The #NULL! error occurs in Excel and not in Google Sheets. Instead, we will see the formula parse error, #ERROR!. It typically occurs in the following cases:

  • An operator is missing in the formula.
  • The counts of opening and closing brackets are unequal in a formula.
  • There is an ‘= sign before a text. However, it is not a formula.

However, we see this error when Google Sheets cannot assess what is wrong with the cited expression.

For example, the source dataset holds a list of stationery items and their units available data in two stores.

Null Error

We must evaluate the total units available, considering all the items and both the stores, and show the sum value in cell B7.

Step 1: Select cell B7, enter the SUM function, and press Enter.

=SUM(B2 C6)

IFERROR in Google Sheets - Null Error - Step 1

The function output is the #ERROR! error value since the function syntax is missing a colon in the specified range term. However, Google Sheets cannot categorize such an error, leading to it displaying the #ERROR! error value.

We shall try resolving the issue by applying the SUM() mentioned above in the IFERROR() in a new target cell, say, B8, so that we can display a meaningful text instead of the error.

Step 2: Choose cell B8, enter the IFERROR(), and press Enter.

=IFERROR(SUM(B2 C6),”An operator is missing in the formula.”)

IFERROR in Google Sheets - Null Error - Step 2

The above example shows that we cannot overcome the #ERROR! error using the IFERROR function in Google Sheets. Thus, the only solution is to check and manually correct the formula syntax to achieve the correct output.

#5 – “#NUM!” Error

The descriptions show specific calculation requirements and we must apply the applicable formulas in column B cells to secure the required output values.

IFERROR in Google Sheets - Num Error

Step 1: Select cell B2, enter the SQRT function, and press Enter.

=SQRT(169)

Num Error - Step 1

Next, choose cell B3, enter the SQRT(), and press Enter.

=SQRT(-625)

Num Error - Step 1 - num

Finally, select cell B4, enter the following expression, and press Enter.

=200^800

IFERROR in Google Sheets - Num Error - Step 1 - expression

The second and third target cells show the #NUM! error since the SQRT() cannot find the square root of a negative number. On the other hand, raising a number to a power, which is massive, will result in a value Google Sheets cannot handle.

However, the better option is to display a meaningful reason instead of the error value by applying the abovementioned formulas in the IFERROR() in the target cells. Let us consider cells C2:C4 as the target cells.

Step 2: Select cell C2, enter the IFERROR(), and press Enter.

=IFERROR(SQRT(169),”Input value is negative.”)

IFERROR in Google Sheets - Num Error - Step 2

Next, choose cell C3, enter the IFERROR function, and press Enter.

=IFERROR(SQRT(-625),”Input value is negative.”)

IFERROR in Google Sheets - Num Error - Step 2 - negative

Finally, choose cell C4, enter the IFERROR function, and press Enter.

=IFERROR(200^800,”Formula output is too big for Google Sheets to process.”)

IFERROR in Google Sheets - Num Error - Step 2 - Output

In each case, the formula in the IFERROR() executes to return a value. The IFERROR() checks if the cited formula output is an error, in which case it returns the text cited as its second argument value. Otherwise, the IFERROR() output is the specified formula’s return value.

#6 – “#REF!” Error

The source dataset contains the inputs mandatory to determine the count of coupon payments for a specific security.

IFERROR in Google Sheets - Ref Error

We shall use the COUPNUM function in cell G1 to determine the required value based on the input data.

Step 1: Select cell G1, enter the COUPNUM(), and press Enter.

=COUPNUM(C2,C3,C4,C5)

IFERROR in Google Sheets - Ref Error - Step 1

Now, assume we delete the last row of the source dataset, which leads to the COUPNUM() returning the #REF! error.

IFERROR in Google Sheets - Ref Error - Step 1 - ref

We can show a valid comment instead of the error value using the COUPNUM() within the IFERROR(). Assume the new target cell is G3.

Step 2: Select cell G3, enter the following IFERROR(), and press Enter.

=IFERROR(COUPNUM(C2,C3,C4,C5),”A reference is missing or got deleted in the formula.”)

IFERROR in Google Sheets - Ref Error - Step 2

Firstly, the COUPNUM() executes, and it returns the #REF! error as the last referenced value got deleted. Next, the IFERROR() checks whether the COUPNUM() output is an error value, in which case it returns the text, specified as its second argument, as the output.

#7 – “#VALUE!” Error

The source dataset shows the input values essential to determine the lowest value for which the accrued binomial distribution exceeds or equals the cited criterion value. Assume the target cell is C6.

IFERROR in Google Sheets - Value Error

We shall use the CRITBINOM function in the target cell to secure the required output.

Step 1: Select cell C6, enter the CRITBINOM(), and press Enter.

=CRITBINOM(C2,C3,C4)

Value Error - Step 1

However, if a supplied argument value is of an incorrect datatype, the function output is the #VALUE! error. For instance, in this case, the first argument value is a text instead of a number, which leads to the function returning the #VALUE! error.

Value Error - Step 1 - value

We can display a valid statement or value instead of the error value using the CRITBINOM() within the IFERROR(). Assume the new target cell is C8.

Step 2: Select cell C8, enter the following IFERROR(), and press Enter.

=IFERROR(CRITBINOM(C2,C3,C4),”A parameter within the formula is a type that the function does not accept.”)

Value Error - Step 2

Firstly, the CRITBINOM() executes, and its output is the #VALUE! error for the same reason cited earlier. Next, the IFERROR() checks if the CRITBINOM() output is an error value, in which case its output is the text cited as its second argument.

IFERROR Function Not Working

The IFERROR function not working scenario is when the error in Google Sheets is the formula parse error, #ERROR!. In this case, the function does not recognize the error and cannot replace it with the cited value.

On the other hand, the specified second argument may be an empty string, 0, or a reference to an empty cell or a cell containing 0. In such cases, the function may appear to be not working correctly. However, the function output is indeed accurate.

Important Things To Note

  • When the supplied value or value_if_error argument value is a blank cell, the IFERROR in Google Sheets considers it as a blank string.
  • Assume the value argument value is an array formula. Then, the IFERROR function in Google Sheets returns an array of output values for every cell in the range specified in the value argument.
  • The IFERROR function in Google Sheets does not work for the formula parse error, #ERROR!.

Frequently Asked Questions (FAQs)

1. What is Google Sheets IFERROR blank formula?

The Google Sheets IFERROR blank formula is as follows:

=IFERROR(value,””)

For example, the source dataset lists products, their order dates and costs.

FAQ 1

We must update the cost data of the products listed in the second dataset based on the source dataset. The cell range F2:F4 is the target range.

Step 1: Select cell F2, enter the VLOOKUP(), and press Enter.

=VLOOKUP(E2,$A$2:$C$6,3,0)

FAQ 1 - Step 1

Next, using the fill handle, we enter the formula in the remaining target cells.

FAQ 1 - Step 1 - fill handle

The function output in the first two target cells is the #N/A error, indicating the function could not find the search values in the lookup range. So, it did not get the required return values.

However, we can apply the VLOOKUP() within the IFERROR() to display a blank cell instead of the error value. Assume the new target range is G2:G4.

Step 2: Choose cell G2, enter the IFERROR() containing the VLOOKUP(), and press Enter.

=IFERROR(VLOOKUP(E2,$A$2:$C$6,3,0),””)

FAQ 1 - Step 2

Finally, utilizing the fill handle, feed the formula in cells G3 and G4.

FAQ 1 - Step 2 - Fill handle

Thus, if the VLOOKUP() returns an error value, the IFERROR() replaces the error value with a blank text.

2. When does Google Sheets if error show 0?

Google Sheets IFERROR shows 0 in the following scenarios:
• The formula, supplied as the first argument value, returns 0 as its output.
• The cited formula returns an error value, and the value that replaces the error value is 0 or a reference to a cell containing 0.

3. What are some similar formulae to IFERROR in Google Sheets?

Some similar formulae to IFERROR in Google Sheets are the ISNA, ISERROR, and ISERR functions.

Download Template

This article must be helpful to understand IFERROR In Google Sheets, with its formula and examples. You can download the template here to use it instantly.

This has been a guide to What Is IFERROR In Google Sheets. Here we explain how to use IFERROR function in Google Sheets with examples and points to remember. You can learn more from the following articles –

Reader Interactions

Leave a Reply

Your email address will not be published. Required fields are marked *