How to Use IFERROR in Google Sheets [with Examples]

Error handling is an important part of data processing. When you perform operations on data, there is almost always a possibility for errors. Good thing Google Sheets is well-equipped to detect these errors and notify you with an error code. However, not all users understand what these codes mean, and neither are these codes pleasant to look at!

To respond to these error messages, Google Sheets also offers error handling functions like IFERROR, IFNA, etc. In this tutorial, we will discuss the IFERROR function in Google Sheets. We will explain the syntax of this function, what it does and how it can be applied in different use-cases.

Different Types of Errors in Google Sheets

Before we think about handling errors, it’s important to first know what kinds of errors we are likely to run into when working in Google Sheets, and how Google Sheets represents these errors.

Here are some of the error codes returned by Google Sheets in the event of an error:

The #NA Error

Simply put, the #N/A error means that a particular value is ‘Not Available’. It is returned when a function is trying to access a cell that cannot be accessed. This type of error usually occurs when you use lookup functions like VLOOKUP or HLOOKUP.

The #DIV/0! Error

The DIV/0! error code is returned when a formula is trying to divide a number by 0. A division by 0 does not make mathematical sense, and can make the computer system unstable. So, to avoid this from happening, Google Sheets stops the computation and returns a #DIV/0 error message.

The #REF Error

The #REF error code is returned when you have an invalid reference in your formula. This kind of error usually occurs in the following cases:

  • when there is a missing reference in your formula
  • when there is a circular reference in your formula
  • when you are trying to lookup a cell that is out of bounds

The #VALUE Error

The #VALUE error code is returned when a parameter in your formula is of a type that is not expected. For example, if you use a text parameter in a function that accepts only numbers, then the function will return a #VALUE error.

The #NAME? Error

The #NAME? error code is usually returned when there is a problem with a formula’s syntax. The error could be due to a spelling mistake, a wrong name range or the presence or absence of quotes in a parameter value.

The #NUM! Error

The #NUM! error code is returned by a formula that has some invalid numeric values. For example, when a function tries to find the square root of a negative number or when it tries to return a number that is too large for Google Sheets to handle.

The #ERROR! Message

This error code is usually returned when Google Sheets is not able to tell what is wrong with your formula. This is Google Sheets’ way of letting you know that it cannot make any sense from your formula, and is thus not able to pinpoint the exact error.

This kind of error might occur in the following cases:

  • You might have forgotten to add an important operator between values in your formula
  • You might have an unequal number of opening and closing brackets in your formula
  • You might have started your text with an equal sign (‘=’), even though you didn’t mean to use a formula.

There may be many other reasons for getting the #ERROR message too. Basically any time Google Sheets does not ‘get it’, it returns a #ERROR! Code.

What Does the IFERROR Function Do in Google Sheets?

The IFERROR function is built to respond to all of the above errors. Whenever you wrap a formula with the IFERROR function, it keeps a lookout for one or more of the above errors. If the formula inside it returns any of these error codes, the IFERROR function either returns a blank text or a text that has been specified by you.

Syntax for IFERROR in Google Sheets

The syntax for the IFERROR function is as follows:

IFERROR(test_value, [value_if_error])

Here,

  • test_value is the value, cell reference or formula that is being tested for error.
  • value_if_error is the value that needs to be returned if the first parameter returns an error. This parameter is optional.

The IFERROR function simply returns the result of the first parameter if there’s no error. Otherwise, it returns the second argument (if specified). If the second parameter is not specified, it returns a blank value.

For example, take a look at the screenshot shown below:

iferror function google sheets
  • The formula in the first row simply returns the value in A2, since there is no error code in its referenced cell.
  • The formula in the second row also returns the value in A3, since there is no error code in its referenced cell.
  • The formula in the third row returns a blank, since there is an error code in cell A4, and we did not specify an alternate text in the second parameter of the IFERROR function.
  • The formula in the fourth row returns the alternate text “Your formula possibly has an error”, since there is an error code in cell A5, and we specified this text in the second parameter of the IFERROR function.

Some Applications of the IFERROR Function

As mentioned before, the IFERROR function’s main task is handling errors. As such, it can come in quite handy in a number of situations where we expect an error likely to take place. Let us look at some use-cases and how this function can be applied.

Returning a Blank in Case of Division by Zero Error

When you perform mathematical calculations with your data, especially when there is division involved, there is always a possibility of a Division by Zero error.

For example in the following screenshot, you can see that we are trying to divide the value of Input A by the difference between values in Inputs B and C:

 Returning a Blank in Case of Division by Zero Error

Notice that in cell E4 we get a #DIV/0/! error. This is because we are trying to divide by the difference between the values in cells B4 and C4 which is 0. This is also causing an error in the final sum (in cell E6).

Division by 0 does not make mathematical sense, so Google Sheets prevents this computation from taking place, thereby returning the error code. This could be due to an error during data entry or due to some other anomaly. The key point here is that it is possible for something like this to happen.

To make sure this error does not affect the rest of the computations, we can use the IFERROR function and get it to return a blank. This way, subsequent computations using these results will not get affected, as shown below:

we can use the IFERROR function and get it to return a blank

All we did was use the IFERROR function with our main formula in the first parameter.

=IFERROR(A2/(B2-C2))

We could have put a blank in the second parameter, but that is unnecessary because the second parameter is considered to be a blank by default unless something else is specified.

Returning a Specific Text with IFERROR in Case of VLOOKUP Error

When you have functions like VLOOKUP or HLOOKUP, there’s always a possibility that a value being looked up does not exist in the lookup table. For example, take a look at the screenshot below:

Returning a Specific Text with IFERROR in Case of VLOOKUP Error

The table on the right is trying to lookup each Name from the lookup table on the left and display the corresponding Score value.

Notice in cell E3 we get a #N/A error message. This makes the whole table look a little untidy. Moreover, a novice user looking at this spreadsheet might not be able to understand what this error message means or why it’s there.

To make your table more readable and intuitive, it would make sense to show some sort of message like ‘Value not found’ instead of an error code. This would help the reader understand exactly what the problem is and take away the guesswork.

For this, the IFERROR function comes in quite handy. All you need to do is wrap the IFERROR function around the VLOOKUP formula, and specify the text you want to display in case there’s an error.

In our case, we just want to say ‘Value not found’, so we specify this in the second parameter of the IFERROR function, as follows:

Value not found

You can also use IFERROR with the QUERY function to take care of #N/A errors in the same way.

Error Handling in Array Formulas

You can also use the IFERROR function with array formulas. For example, in the screenshot below, we used an array formula to quickly divide values of column B by values of column A, thereby giving us all the results of column C with a single formula:

 Error Handling in Array Formulas

Notice the result of row 4 shows up as a #DIV/0 error, since we are trying to divide the value in B4 by the value in A4, which is 0.

The IFERROR function can also be applied here, but you have to make sure that you wrap the ARRAYFORMULA function within the IFERROR function and not the other way round. This means, your formula should be:

=IFERROR(ArrayFormula(A2:A5/B2:B5))

This made the IFERROR function apply to every value in the returned array. Here’s the result you get when you apply the above formula to the given data:

 =IFERROR(ArrayFormula(A2:A5/B2:B5))

Limitations of the IFERROR Function

The IFERROR function is like a generic solution to all kinds of errors. However, the downside is that this function does not differentiate between the different error messages and treats them all the same way.

So whether you get a #REF! error or a #VALUE error, the returned value is always the same. This might make it difficult for the user to troubleshoot, as they would not understand what kind of error they are dealing with.

Moreover, hiding errors with an IFERROR function that returns a blank value can also be counter-intuitive as it makes it difficult for the user to understand whether there is an error in the first place or not. In this way the user cannot trace back to the source of a problem, as they won’t even know that it’s there!

To handle specific types of errors, you can use the specific functions meant for handling those functions. For example, to handle N/A errors, you can use the IFNA function.

Conclusion

In this tutorial, we discussed error handling using IFERROR in Google Sheets. We pointed out the different types of errors that you are likely to come across in Google Sheets and showed you how you can apply the IFERROR function to either hide the error messages or replace them with a more meaningful text. We hope you found this tutorial helpful.

Nahid

Nahid

Nahid Akhter is a writer, programmer, and online course content creator. She has an MS in Computer Science and has been in the education line for more than 14 years. Her specialization is in programming and Tech-writing, and her areas of interest include Office productivity, Artificial Intelligence, and Web design and development.

Best Online Google Sheets Courses

Become a Google Sheets expert today!
View Course