When working with formulas in Google Sheets, sooner or later, you’ll see an error message returned. These can take a variety of forms, each providing information on the type of error that has been encountered. Careful checking and proofreading of your formulas can help you avoid some of them. However, while not all errors are avoidable, some can be predicted. For example, it’s relatively easy to predict #N/A errors with lookup functions like VLOOKUP, HLOOKUP, or QUERY. If you’re dividing cell values by other cell values, some of these may be blank and lead to #DIV/0! errors. In these cases, you can use the IFERROR function to control what the function returns.
In this guide, you will learn about the IFERROR function and its syntax. You will review common error messages and their causes and how you can use IFERROR to avoid ugly or inexplicable messages in your spreadsheet’s cells. You have step-by-step instructions on how to use IFERROR to achieve different results, like returning a custom message or a blank. Finally, you have an example of how to nest IFERROR and VLOOKUP functions to try alternative lookups in case of errors.
What is the IFERROR Function in Google Sheets?
You can wrap the IFERROR function around your formulas and other built-in functions to control the cell’s output when an error occurs. The function’s second parameter lets you specify what happens when it encounters an error: IFERROR(value,value_if_error*). If you skip this parameter, it automatically returns a blank.
Why should I use IFERROR in Google Sheets?
Errors are always a possibility when using formulas or built-in functions, but in some cases, they are very predictable. For example, there’s a good chance you’ll see the #N/A error while using VLOOKUP. This is just how the function behaves when it can’t find the search_key. Division by a variable that is sometimes blank will lead to the #DIV/0! error message, which you may want to replace with “0” or a blank, depending on the case.
The list below includes some common error messages and the type of error they indicate.
- #DIV/0! - Indicates that you’re asking Google Sheets to divide a value by zero, which isn’t possible.
- #NUM! - Indicates that Google Sheets cannot calculate or display the value you requested.
- #N/A - Indicates that the value you want is unavailable, usually when using lookup functions.
- #NAME? - Indicates that you have referenced a named entity that Google Sheets doesn’t recognize, like a misspelled name range or built-in function.
- #REF! - Indicates that you have provided an invalid reference, frequently due to deleting cells or named ranges used in the formula.
- #VALUE! - Indicates that you have provided a data type different than your formula requires.
- #ERROR! - Usually indicates the presence of an unexpected typo; for example, a missing or extra character or operator.
In any case, you can use IFERROR to replace the default error message with something more informative or useful. While it’s possible to use it to hide errors, it’s usually not a great idea to do this. If you’re concerned about appearance, you customize the message to something more discreet.
To learn more about formula parse errors and how to fix them, check out our guide on How to Fix Formula Parse Error in Google Sheets.
How do I Use IFERROR in Google Sheets?
If you suspect that one of your formulas might sometimes result in an error, you can wrap the IFERROR function around it. The second parameter determines what to return if there’s an error, and this can be a custom message, a value, or even a different calculation.
In the example below, the IFERROR function will return a custom message when VLOOKUP can’t find a value.
- 1. In an empty cell, add the IFERROR function.
- 2. Add the function or formula you want to use as the first parameter. In this case, you want to use VLOOKUP to find a specific value.
- 3. In the second parameter, you can specify what is returned in case of an error, including a custom message or another calculation.
- 4. Close the parenthesis and press ‘Enter’ to see the result.
Examples of How to Use IFERROR Function in Google Sheets
Now that you know how to use IFERROR to return a custom message, it’s time to see some examples using different options. In the first example, you will learn how to use the function to return a blank or a numerical value. In the second example, you’ll learn how to nest IFERROR functions to provide alternative formulas to try in case of errors.
When sharing a Google Sheets spreadsheet Google usually tries to share the entire document. Here’s how to share only one tab instead.
READ MOREExample 1. IFERROR Return Blank or Value
Imagine you want to determine the unit price of various purchases by dividing the total amount by the quantity. However, you know that the quantity value may sometimes be blank, so you can expect some #DIV/0! error messages. In these cases, you can use IFERROR to return a blank or a zero instead.
- 1. Type the IFERROR function, followed by the formula you want to use.
- 2. To return a blank, you can skip the second IFERROR parameter. If you want to return a zero, use the number as the second parameter. Close the parenthesis and press ‘Enter’ to see the result.
- 3. Grab the fill handle and drag it down to copy the formula.
If you work with important data in Google Sheets, you probably want an extra layer of protection. Here's how you can password protect a Google Sheet
READ MOREExample 2. IFERROR Return Alternative Function
Follow the steps below to nest multiple IFERROR functions to try different options if the value you want isn’t found. For example, if you’re unsure whether you have a first or last name, you can check both columns in turn.
- 1. Type the IFERROR function and add your lookup function to find the search key in the ‘last_name’ column. Close the parenthesis and add a comma.
- 2. For the second parameter, add another IFERROR function followed by the lookup function. In this case, you want to look for the search key in the ‘first_name’ column. Close the parenthesis and add another comma.
- 3. Finally, add the message you want to return if neither lookup is successful.
- 4. Close both parentheses and hit ‘Enter’ to see the result. Accept the Autofill suggestion or drag down the fill handle to copy the formula.
Conclusion
The IFERROR function in Google Sheets is very useful. The function allows you to control what it will return if it encounters an error. You know that IFERROR allows you to avoid having default error messages in your cells, which can sometimes be distracting. However, simply hiding errors is not a long-term solution and is only useful in specific situations.
You also know that replacing the default error message with a more informative one works well in many situations, such as when you’re looking up values that may not be there. Finally, you know how to nest IFERROR functions to try an alternative calculation or lookup if the first produces an error.
Check out our guides below to learn more about formula parse errors, as well as lookup functions in Google Sheets.