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.

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

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

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