## 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

IFERRORfunction 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.

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.

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.

##### Table of contents

###### Key Takeaways

- The
**IFERROR**in Google Sheets - 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**, - We can utilize the
**IFERROR**function in Google Sheets as an individual function. However, applying the function with other inbuilt functions, such as**VLOOKUP**,

### IFERROR() Google Sheets Formula

The **IFERROR **function in Google Sheets syntax is the following:

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:

**Access the function from the ribbon.****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.

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.

Further, we can click the ‘**?**’ symbol against the function name to see its 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.

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

- Click the cell where we want to display the result.
- Type
**=IFERROR(**in the cell. [ Alternatively, type**=I**or**=IF**and click the function name**IFERROR**from the suggestions to select the function.] - Supply the argument values, separated by commas, and close the brackets.
- 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.

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)*

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

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”)*

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

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

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

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

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

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.

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*

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

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.”)*

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

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.

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)*

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.”)*

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 ‘
**=**’

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.

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)*

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.”)*

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.

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

*=SQRT(169)*

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

*=SQRT(-625)*

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

*=200^800*

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.”)*

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

*=IFERROR(SQRT(-625),”Input value is 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.”)*

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.

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)*

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

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.”)*

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.

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)*

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.

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.”)*

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 - 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.

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)*

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

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),””)*

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

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.

### Recommended Articles

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 –

## Leave a Reply