Excel ISREF Function

Learn how to use the ISREF function to check if a value is a valid cell reference.

What is the ISREF Function?

The ISREF function in Excel is a logical function used to check whether a given value is a reference. It returns `TRUE` if the value is a valid reference to a cell or a range of cells, and `FALSE` otherwise.

This function is part of the "IS" group of functions, which are often used to test the result of a formula or the value in a cell before performing another calculation.

Syntax

The syntax for the ISREF function is straightforward:

=ISREF(value)
  • value (required): This is the value you want to test. It can be a cell reference, a named range, a formula, or a value typed directly into the function.

Basic Examples

Let's look at some simple examples to understand how ISREF behaves.

Testing a Single Cell Reference

If you test a direct reference to a cell, ISREF returns TRUE.

=ISREF(A1) --> Returns TRUE

Testing a Range Reference

It also returns TRUE for a range of cells.

=ISREF(A1:B5) --> Returns TRUE

Testing a Non-Reference Value

When the value is not a reference (like text or a number), it returns FALSE.

=ISREF("Hello") --> Returns FALSE
=ISREF(123) --> Returns FALSE

Testing a Formula Result

If you test a formula that returns a value (not a reference), you will get FALSE.

=ISREF(SUM(C1:C10)) --> Returns FALSE

Practical Use Cases

While simple on its own, ISREF becomes powerful when combined with other functions like IF or INDIRECT, especially for creating robust templates and validating inputs.

Validating Named Ranges with IF

Imagine you have a template where a user is supposed to define a named range called "SalesData". You can use ISREF to check if they have created it before attempting to use it in a calculation.

=IF(ISREF(SalesData), SUM(SalesData), "Error: 'SalesData' named range not found.")

This formula first checks if `SalesData` is a valid reference. If TRUE, it calculates the SUM. If FALSE, it displays a helpful error message instead of the generic #NAME? error.

Combining with the INDIRECT Function

The INDIRECT function turns a text string into a valid cell reference. You can use ISREF to check if the text string can be successfully converted into a reference. Note that if INDIRECT fails, it produces a #REF! error, so we should wrap it in IFERROR.

Let's say cell B1 contains the text "A1".

=ISREF(INDIRECT(B1)) --> Returns TRUE (because INDIRECT("A1") becomes the reference A1)

Now, let's say cell B2 contains the text "InvalidText".

=IFERROR(ISREF(INDIRECT(B2)), FALSE) --> Returns FALSE (INDIRECT fails, IFERROR catches it and returns FALSE)