ISREF Function Examples – Excel & Google Sheets

This tutorial demonstrates how to use the ISREF Function in Excel to test if a cell contains a reference.

How to use the ISREF Function

The ISREF Function tests if an input is a valid cell reference or not.

=ISREF(A1)

Here, A1 and A1:A7 are valid cell references so the ISREF Function returns TRUE for those inputs.

Deleted References

Let’s delete column A from the above example. After deleting column A, the ISREF Function returns FALSE for references to former column A.

You can see that one of the primary uses of the ISREF Function is to test if a range still exists or if it had been deleted.

Does Worksheet Exist

Another possible usage of the ISREF Function is to test if a worksheet exists. In this example, we will enter a cell reference to another worksheet =Sheet2!A2 .

=ISREF(Sheet2!A1)

Because the worksheet exists, the cell reference is valid and TRUE is returned.

Now let’s delete Sheet2 and you can see that the value is now FALSE.

INDIRECT and OFFSET

Excel provides you with two functions to dynamically create ranges: INDIRECT and OFFSET. By using these functions with ISREF, you can test if a range is valid.

Let’s look at an INDIRECT example to test if a worksheet exists by referring to the worksheet in a cell.

=ISREF(INDIRECT("A1"))
=ISREF(INDIRECT("Sheet1!"&"A1"))
=ISREF(INDIRECT("[Book1.xlsx]"&"Sheet1!"&"A1"))

Now you can change the worksheet input to test if various sheets exist.

Next, let’s look at an OFFSET example. Here we will test if the range resulting from the OFFSET Function is valid.

=ISREF(OFFSET(D3,3,3))

The second example, returns an invalid range (Row < 1), so ISREF returns FALSE.

You might use these function combinations along with an IF Statement like this:

=IF(ISREF(OFFSET(D3,3,3)),OFFSET(D3,3,3),"invalid range""")

 

ISREF in Google Sheets

The ISREF Function works exactly the same in Google Sheets as in Excel: