People get confused over the use of ISREF function in Google Sheets. In real sense, the ISREF function in Google Sheets has limited role. It just checks a value for a valid cell reference and returns TRUE or FALSE. But when using with INDIRECT function, ISREF formula can also check a cell content for valid cell reference.
Many users complain that most of the time ISREF function returns TRUE. I’ll tell you why this so.
ISREF Function in Google Sheets
The function ISREF is one of the function coming under the type INFO. This type of functions just use to check provided values and pass INFORMATION such as TRUE or FALSE.
Syntax of Google Sheets ISREF Function
Syntax:
ISREF(value)
How to Use ISREF Function in Google Sheets
As already told, this function seems quite confusing for many. So here is the examples.
ISREF Formula Examples
Below you can find few examples to ISREF function.
Example 1:
When just type the formula as above, ISREF returns TRUE as the value inside the formula A1 is a cell reference. The content, if any, in Cell A1 has no relation with this formula. But the below formula would return FALSE as the value is a string, not a cell reference.
=ISREF(“A1”)
Example 2:
Now see the below example why people get confused over the use of ISREF.
In the above case, I’ve just put the text A2 in cell A1. ISREF can’t directly check the content in a cell for its validity as cell reference. Google Sheets INDIRECT function can only identify the reference in a cell. But instead of testing whether the provided value in a cell is a valid cell reference, INDIRECT function fetch the content from the referred cells if found TRUE.
Here Indirect function first checks the value in cell A1 and if TRUE, then fetch the content from the referred cell A2. But an ISREF and INDIRECT combo can test the value inside a cell and return TRUE or FALSE. See couple of examples below.
Example 3:
Here Formula in B3 returns error as the value in A3, “Info Inspired” is not a cell reference. Similarly Cell A6 contain a sheet name. Since I don’t have a sheet name as “Sheet56”, obviously the formula would return FALSE. If I add a new sheet in that name, the formula would then return “TRUE”.
From the above example, one thing is sure. ISREF function is useful when using with INDIRECT function. You can use this combo in logical tests as below.
Example 4:
Since there is no Sheet56, this logical test execute the False part of the formula and return the text “WRONG REFERENCE”. When there is any sheet in that name, it would then fetch the content from the cell A1:E1.
As already told, Google Sheets ISREF is an INFO type function and it has limited use.
