If you work with formulas in Google Sheets, you know that errors can pop up any time.
While getting errors is a part of working with formulas in Google Sheets, it’s important to know how to handle them correctly.
In this tutorial, I will show you how to use the IFERROR Google Sheets function to handle these errors.
Before we get into the formula itself, let’s quickly learn about the different errors you may face in Google Sheets and the possible reasons for these errors.
This Article Covers:
What is the IFERROR Google Sheets Function?
The IFERROR Google Sheets function searches for errors in the data range and returns the first argument when there’s no error and a blank cell or a specified value when there’s an error.
IFERROR Function Syntax
IFERROR(value, [value_if_error])
Input Arguments
- value – this is the argument that is tested for error. It could be a cell reference or a formula.
- value_if_error – this is an optional argument. If the value argument is an error, this is the value that is returned in place of the error. The following error types evaluated: #N/A, #REF!, #DIV/0!, #VALUE!, #NUM!, #NAME?, and #ERROR!.
Additional Notes:
- If you omit the ‘value_if_error’ argument, the cell displays nothing in case of an error (i.e., a blank cell).
Different Types of Errors in Google Sheets
Here are the different errors you may encounter when working with Google Sheets:
#DIV/0! Error
You’re likely to see this error message when a number is divided by 0. This is called the division error. If you hover over the cell with this error, it will show – “Function DIVIDE parameter 2 cannot be zero.”
#N/A Error
This is called the ‘not available’ error and you will see this error code when you use a lookup formula and it can’t find the value (hence Not Available).
Below is an example where the VLOOKUP formula is used but returns an error when it can’t find the lookup value in the table array.
#REF! Error
This is called the reference error, and you will see this alert message when the reference in the formula is no longer valid. This could be the case when the formula refers to a cell reference, and that cell reference does not exist (happens when you delete a row/column or worksheet referred to in the formula).
#VALUE! Error
The value error occurs when you use an incorrect data type in a formula.
In the below example, when I try to add cells that have 0 and the character A, it gives the value error. This happens as you can only add numeric values, but instead, I tried adding a number with a text character.
#NAME? Error
This error is likely a result of a misspelled function. For example, if instead of VLOOKUP, you by mistake use VLOKUP, it will give a name error.
#NUM! Error
Num error can occur if you try and calculate a very large value in Google Sheets. For example, =145^754 will return a number error.
It can also result when you try and give a non-valid number. For example, in the case of the Square Root function, if you give a negative number as the argument, it will return a number error.
Now I hope you have a better understanding of the possible errors you can get in Google Sheets and what might result in such errors. You can parse these errors, but sometimes you can’t do so without changing the original data.
Now, let’s understand how to use the Google Sheets IFERROR function to handle all these errors.
Related:Â how to count if cells aren’t blank in Google Sheets
How IFERROR Works
The Google Sheet If error function works by scanning for errors in your sheet and hiding them by replacing them with a blank cell or a specified value. You use it by wrapping the formula that is returning the errors with the IFERROR formula. In case there is no error, the formula simply returns the correct value.
IFERROR function works with all kinds of functions like QUERY, LOOKUP, and ARRAY functions. If the value argument is an array formula, IFERROR will return an array of results for each item in the range specified in value. Let’s look at how to use IFERROR in Google Sheets.
Using IFERROR Function in Google Sheets – Examples
Here are a couple of examples of using the IFERROR function in Google Sheets.
Example 1 – Return a Blank or Meaningful Text in Place of the Error
You can easily create conditions where you specify a specific value in case a formula returns an error (for example, if error then blank, or if error then 0).
If you have formula results that result in errors, you can use the Google Sheet IFERROR function to wrap the formula in it, and in case of error return a blank or a meaningful text.
In the below dataset, the calculation in column C returns an error if the quantity value is 0 or blank.
In this case, you can return a blank instead of error by using the following formula:
=IFERROR(A2/B2,"")
In this case, you can also use some meaningful text instead of returning a blank cell.
For example, the below formula would return the text “Error” when the calculation gives an error value.
=IFERROR(A2/B2,"Error")
Example 2 – Returning ‘Not Found’ when VLOOKUP can not Find a Value
With VLOOKUP functions, you get a #N/A! error when the function can not find the lookup value in the table array.
You can use the IFERROR function Google Sheets to return a meaningful text, such as ‘Not Found’ or ‘Not Available’, instead of the error.
Below is an example where the VLOOKUP function returns the #N/A! error.
Below is the IFERROR VLOOKUP Google Sheets formula you can use to return the text “Not in List” instead of the error.
=IFERROR(VLOOKUP($D$2,$A$2:$B$5,2,0),"Not in List")
Note that you can also use the IFNA function instead of the IFERROR function. Remember that the IFERROR function would remove any type of error, while IFNA would only treat the #N/A! error.
Related: Using IFERROR Function in Excel.
When to Use the IFERROR Function
The IFERROR Google Sheets function lets you specify what to return in case the results of a formula is an error. It can be used:
- For hiding errors when cleaning up a document.
- Using lookup and some of the values are not in the original data.
- When sorting data with specific conditions.
- When you want to find errors in your worksheet, like spelling mistakes.
When to Not Use the IFERROR Function
There are also situations when you shouldn’t use the IFERROR Google Sheets function:
- When working with cell references instead of actual values.
- When working with a range of cells that are constantly changing.
Using IFERROR With Arrays
The array formulas are used to calculate a range of values against another range. You can insert the if error Google Sheets formula in the ARRAYFORMULA to remove errors in the results.
For example, in our sheet below we have some errors as a result of the array formula: =ARRAYFORMULA(A2:A7/B2:B7)
To remove the errors, we can add the IFERROR formula into the array formula:
 =ARRAYFORMULA(IFERROR(A2:A7/B2:B7,""))
This formula works with the argument in Google Sheets if error then blank. The IFERROR Google Sheets function returns a blank cell for the errors for each item in the range of cells specified.
You can also make the argument for the Google Sheets if error show 0 by adding a 0 into the quotation marks.
Similar Functions to IFERROR
The Google spreadsheet IFERROR function is very useful in getting rid of errors in your sheet and replacing them with a blank or a different value. Similar functions include:
- IFNA: returns a blank cell or a specified value if the formula returns `#N/A`
- IF: Returns a specified value if a logical expression is `TRUE` and another if it is `FALSE`.
- ISERROR: Identifies if a formula returns an error.
- ISERR: Identifies if a formula returns an error other than `#N/A`.
Wrapping Up
The IFERROR function is useful for finding and hiding errors in Google Sheets. In this article, we showed you how to use the IFERROR Google Sheets function. If you found this article useful, be sure to check out our guide to the IF function in Google Sheets.
You May Also Like the Following Google Sheets Tutorials:
