If you work with formulas in Google Sheets, you would 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 these errors in the right way.
In this tutorial, I will show you how to handle errors in Google Sheets using the IFERROR function.
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.
Different Types of Errors in Google Sheets
Here are the different errors you may encounter when working with Google Sheets:
You're likely to see this error when a number is divided by 0. This is called the division error. If you hover over the cell that has this error, it will show – “Function DIVIDE parameter 2 cannot be zero.”
This is called the ‘not available' error and you will see this when you use a lookup formula and it can't find the value (hence Not Available).
Below is an example where VLOOKUP formula is used but returns an error when it can't find the lookup value in the table array.
This is called the reference error and you will see this 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 that was referred in the formula).
The value error occurs when you use an incorrect data type in a formula.
For example, 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.
This error is likely to 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 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 case of 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 what the possible errors you can get in Google Sheets and what might result in such errors.
Now, let's understand how to use IFERROR function in Google Sheets to handle all these kinds of errors.
IFERROR Function Syntax
- 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!.
- If you omit the ‘value_if_error' argument, the cell displays nothing in case of an error (i.e., a blank cell).
- If the value argument is an array formula, IFERROR will return an array of results for each item in the range specified in value.
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
If you have formula results that are resulting in errors, you can use the 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:
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.
Example 2 – Returning ‘Not Found' when VLOOKUP can not Find a Value
With VLOOKUP function, 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 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 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.
You May Also Like the Following Google Sheets Tutorials: