How to Use ISERROR Function in Google Sheets and Error Handling

0
113
How to Use ISERROR function in Google Sheets

Now I am seriously thinking about writing a tutorial that covering different error handling methods in Google Sheets. Because there are different functions in Google Sheets that you can use to find and remove errors. Here I am detailing about one such function called ISERROR. You can learn here how to use Iserror function in Google Sheets to find and eliminate errors like #DIV/0!, #N/A, #NAME?, #VALUE!, #REF!, #NULL!, and #NUM!. When such errors returned by any formulas, you can replace that error with custom values or Boolean TRUE or FALSE.

How to Use ISERROR function in Google Sheets

Purpose:

You can use Google Doc Spreadsheets Iserror function to return TRUE if a formula output is an error value. The error value (or values in case of array formula) includes errors which I’ve mentioned above. Now see the syntax of Google Sheets Iserror function.

Syntax: ISERROR(value)

Examples

You can quickly learn the use of Google Sheets Iserror formula from the below examples.

iserror formula normal use in google sheets

As you can see there are error values in cell D4 and D6 due to the division by 0. You can point your cursor to the said cells to see the error reporting by Google Sheets. It’s as follows.

Function DIVIDE parameter 2 cannot be zero.

Here you can make use of Google Sheets ISERROR function as below to return a custom text in the cells that containing errors.

use IF with Iserror in Google Doc Sheets

To customise the error values, you can use IF function with Iserror and that’s what I’ve done in the above example. See how I’ve removed error values in Cell D4 and D6. There I’ve used IF function to return the text “Error Value”. If you use the Iserror independently, the formula results in D4 and D6 would be TRUE and in other cells in the column FALSE.

Formula:
=
iserror(C6/B6)
Result:
TRUE

Similar: Google Sheets IFERROR Function – Usage and Examples

It’s important to know the use of Iserror function in ArrayFormula. I am using the above same example but this time in the form an Array Formula. In the formula, the Iserror should come after the ArrayFormula.

how to use iserror in an array formula in google sheets - example

Now let’s move to one more example where I’m using Iserror in a Vlookup.

iserror in vertical lookup

In this Vlookup formula, the lookup value (search key) “Lydia Phillips” is not available in the first column range A2:A4. For this reason Vlookup returns #N/A error. In this case you can use Google Sheets Iserror formula as below.

Formula:
=iserror(vlookup(D3,A2:B4,2,FALSE))
Result:
TRUE

Since the value is error, Iserror returns TRUE. As mentioned in earlier example, you can use a custom message instead of TRUE using IF. Hope you could understand how to use Iserror function in Google Sheets.

LEAVE A REPLY

Please enter your comment!
Please enter your name here