Welcome to another exciting tutorial from Crawlan.com! Today, we will dive into the world of the ISERROR function in Google Sheets. You’ll learn how this powerful tool can help you find and remove errors in your spreadsheets. So grab a cup of coffee and let’s get started!
Syntax and Arguments
Before we begin, let’s understand the syntax of the ISERROR function. It’s quite simple: ISERROR(value). The value can be a cell reference, a formula, or a hardcoded value. The function will return TRUE if the value is any error, including #DIV/0!, #N/A, #NAME?, #NULL!, #NUM!, #VALUE!, and #REF!. On the other hand, it will return FALSE if the value is not an error.
How to Use the ISERROR function in Google Sheets
Let’s jump right into some practical examples to help you grasp the concept of the ISERROR function.
Basic Formula Examples
Example #1
In this example, we have two formulas that return a #DIV/0! error because the divisor is 0 (zero). To handle this error, you can use the ISERROR function along with the IF function.
=C4/B4
=C6/B6To replace the error value with a custom text, you can use the following formula:
=IF(ISERROR(C2/B2),"ERROR VALUE",C2/B2)Simply insert this formula into cell D2 and drag it down to cell D6. This will display “ERROR VALUE” in the cells that contain errors.
Example #2: Using ISERROR with VLOOKUP
In this example, we have a vertical lookup formula that returns a #N/A error because the lookup value is not available in the range. To handle this error, you can use the ISERROR function with VLOOKUP.
=VLOOKUP(D3,A2:B4,2,FALSE)To return a custom message instead of the error, you can use the following formula:
=IF(ISERROR(VLOOKUP(D3,A2:B4,2,FALSE)),"Not found",VLOOKUP(D3,A2:B4,2,FALSE))Example of Using the ISERROR Function in an Array Formula to Handle Errors
The ISERROR function can also be used in conjunction with the ArrayFormula function to apply it to multiple cells at once. This makes it even more efficient and powerful. Here’s an example:
=ArrayFormula(IF(ISERROR(C2:C6/B2:B6),"ERROR VALUE",C2:C6/B2:B6))This formula replaces error values in the range C2:C6 with the text “ERROR VALUE”. It avoids repetitive calculations, improving performance.
Difference Between ISERROR and IFERROR in Google Sheets
Now that you’ve mastered the ISERROR function, let’s take a quick look at its counterpart, the IFERROR function. Although they both handle errors, they serve different purposes.
- ISERROR: Returns TRUE if the value in the specified cell is an error, and FALSE otherwise. It’s useful for testing whether a value is an error, counting error values, highlighting error cells, and masking errors using the IF function.
- IFERROR: Returns the value in the specified cell if it’s not an error, and a different value if it is an error. It’s useful for masking errors and returning alternative values.
Here’s an example of using ISERROR to count the number of error values returned by an array formula:
=COUNTIF(ArrayFormula(ISERROR(B2:B8/C2:C8)),TRUE)This COUNTIF formula counts the number of TRUE values returned by the ISERROR formula.
That’s it for our tutorial on the ISERROR function in Google Sheets. We hope you found it valuable and that it helps you excel in your spreadsheet endeavors. For more exciting tutorials and tips, visit Crawlan.com. Happy Sheets-ing!
