Summary

The Excel ISREF returns TRUE when a cell contains a reference and FALSE if not. You can use the ISREF function to check for a reference in a formula.

Purpose 

Test for a reference

Return value 

A logical value (TRUE or FALSE)

Syntax

=ISREF(value)
  • value - The value to check.

How to use 

The ISREF function returns TRUE to test for a reference in a formula. The ISREF function takes one argument, value, to test. If value is a valid cell reference, range, or named range, ISREF returns TRUE. If value is not a reference, ISREF returns FALSE. ISREF does not evaluate the contents of a reference, just the reference itself.

Examples

ISREF returns TRUE when value is a reference and FALSE if not:

=ISREF(A1) // returns TRUE
=ISREF(A1:C1) // returns TRUE
=ISREF(Sheet1!A1) // returns TRUE
=ISREF("apple") // returns FALSE
=ISREF(100) // returns FALSE
=ISREF(ZZZ1) // returns FALSE

Some functions, like INDEX and OFFSET, can return a reference. As long as the reference is valid, ISREF returns TRUE:

=ISREF(INDEX(A:A,10)) // returns TRUE
=ISREF(OFFSET(A1,1,1)) // returns TRUE

To evaluate a reference as text (i.e. "A1"), use the INDIRECT function:

=ISREF(INDIRECT("A1")) // returns TRUE
=ISREF(INDIRECT("ZZZ1")) // returns FALSE

Notes

Dave Bruns Profile Picture

AuthorMicrosoft Most Valuable Professional Award

Dave Bruns

Hi - I'm Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.