Error handling is an important part of data processing. When you perform operations on data, errors are almost always possible. Good thing Google Sheets is well-equipped to detect these errors and notify you with an error code.
You can use processes like the IFERROR Google Sheets function to work with these potential issues. This tutorial will discuss how to use the IFERROR function in Google Sheets. Read on to learn more.
Table of Contents
Different Types of Errors in Google Sheets
Before we think about handling errors, it’s essential 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’. This error occurs when you use lookup functions like VLOOKUP or HLOOKUP. It is returned when a function is trying to access a cell that cannot be accessed.
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.
Basically any time Google Sheets does not ‘get it’, it returns a #ERROR! Code.
How Does the IFERROR Function Work in Google Sheets?
The Google spreadsheet 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 cell or a text that has been specified by you.
Related: Full Guide to ISERROR in Google Sheets.
Syntax for IFERROR in Google Sheets
The syntax for the Google Sheets 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:
- 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 cell, 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 Google Sheet IFERROR function’s main task is handling errors. As such, it can come in quite handy in several situations where we expect an error likely to occur. Let us look at some use cases and how you can apply this function.
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:
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.
To make sure this error does not affect the rest of the computations, we can use the IFERROR sheets function and get it to return a blank cell. This way, subsequent computations using these results will not get affected, as shown below:
All we did was use the IFERROR Google Sheets 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:
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. 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 Google Sheets function comes in quite handy. All you need to do is wrap the Google Sheet if error formula 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:
You can also use IFERROR with the QUERY function to take care of #N/A errors in the same way.
Using IFERROR with Array Formulas
You can also use the IFERROR function in Google Sheets 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:
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 Google Sheets 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 formula apply to every value in the returned array. Here’s the result you get when you apply the above formula to the given data:
When to Not Use IFERROR Function
The IFERROR function is like a generic solution to all kinds of errors. However, there are times when to use and when to not use IFERROR.
- When you want to differentiate between the different errors. The downside is that this function leads to different unexplained error messages and treats them all the same way.
So whether you get a #REF! error or a #VALUE error value, 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.
- When you want to track back to the source of the error. Hiding errors with an IFERROR formula that returns a blank cell 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.
Frequently Asked Questions
What Are Some Similar Formulae to IFERROR in Google Sheets?
There are a number of formulas similar to the IFERROR:
- ISNA function determines whether a value in a cell or formula is the error `#N/A`. It returns a logical expression true or false depending on whether the error `#N/A` is detected.
- ISERROR function determines whether a value is an error. It returns a logical expression true or false depending on whether there is any error detected.
- ISERR function determines if a value is any other error except `#N/A`. It returns a logical expression true or false depending on whether there is any error except `#N/A` detected.
- IF function returns one value if a logical expression is `TRUE` and another if it is `FALSE`.
Can You Combine IFERROR and VLOOKUP?
Yes, we usually use IFERROR with VLOOKUP to return something instead of the #N/A error in case there is a blank cell in the data range for the formula.
Conclusion
In this tutorial, we discussed how you could apply the IFERROR Google Sheets function to hide or replace error messages with more meaningful text. We hope you found this tutorial helpful.
Related:
