How To Use ISREF Function in Google Sheets

The ISREF function in Google Sheets is useful when you need to check if a certain value is a valid cell reference.

Cell references include references to a single cell, such as A2, or a range of cells such as A1:C10. The ISREF function in Google Sheets does not look at the contents of the cell but rather at the cell reference itself.

The rules for using the ISREF function in Google Sheets are as follows:

• The function requires a single argument (the value to verify as a valid cell reference)
• The function then outputs a Boolean value. The result is TRUE if the cell reference is valid and FALSE otherwise.
• The ISREF function does not validate the contents of a reference, just the reference itself.

Let’s take a look at a quick use-case of when we can apply the ISREF function in Google Sheets. 

We have a spreadsheet with a list of cell references. Unfortunately, such information was transcribed from a physical workbook. This means it is possible to have invalid cell references in the list. Is it possible to know whether a cell reference is valid?

It’s easy to find this out using the ISREF function. With some help from the INDIRECT function, we can take any string representing a cell reference and check if the string is a valid cell reference.

Go read the next section to learn how to write the ISREF function ourselves in Google Sheets and later test out the function with sample data.

The Anatomy of the ISREF Function

So the syntax of the ISREF function is as follows:

=ISREF(value)

Let’s dissect this formula and understand what each of these terms means:

• = the equal sign is used to start any Google Sheets function.
• ISREF() is our ISREF function. It checks whether a value is a valid cell reference.
• value refers to the value to be verified as a cell reference.
• A string of a valid text reference will still be considered text and not a valid cell reference.
• To evaluate a reference as text, we can use the INDIRECT function.

A Real Example of Using ISREF Function

 Let’s explore a real example of the ISREF function being applied to a Google Sheets spreadsheet.

In the example below, we have a set of ISREF formulas. The values in column A show the result, while column B shows the formula used. Values such as A1, A6, and A2:B7 are valid cell references.  

We can also check if a list of strings contains valid cell references. We used the INDIRECT function to convert our list of strings into actual cell references in the spreadsheet below. These references are then plugged into the ISREF function to return the final verdict.

To get the results in column B, we just need to use the following formula:

=ISREF(INDIRECT(A2))

You can make a copy of the spreadsheet above using the link I have attached below. 

If you’re ready to use the ISREF function in Google Sheets yourself, read on and follow the step-by-step guide in the next section.

How to Use ISREF Function in Google Sheets

1. In this example, we’ll be using ISREF and INDIRECT to determine whether certain strings are valid cell references. As seen below, we begin by selecting a cell to place our formula.
   
   
   
2.  In the next step, we just have to type the equal sign ‘=’, followed by ‘ISREF(‘.
3. A pop-up box may appear with information on the ISREF function. We can click on the arrow on the top-right-hand corner of the box to minimize it.
   
   
   
4. The next step is to type in our argument. For this example, our arguments must first pass through the INDIRECT function, which converts the string to an actual cell reference.
5. Next,simply hit Enter on your keyboard to let the function return the final result. In this case, A1 was identified as being a valid cell reference.
   
6. Finally, we can drag down the formula in cell B2 to fill in the rest of the column!

Frequently Asked Questions (FAQ)

1. Does the ISREF function take different sheets into consideration?
   Yes, the ISREF function takes sheets into consideration. If a cell reference includes the sheet name, such as “Sheet!A1” or “Raw!B3”, then that sheet must exist for the cell reference to be marked valid.
   
    
2. Does the ISREF function consider the INDEX and OFFSET functions as cell references?
   As long as the cells referenced by INDEX and OFFSET exist, the ISREF function will mark them as valid cell references.
   In the example below, =INDEX(C:C,2) is another way of saying =C2, which is why the ISREF function returns TRUE.
   
   
   

That’s all you need to know to start using the ISREF function in Google Sheets. This step-by-step guide shows how simple it is to determine whether a given input is a valid cell reference.

You can now combine the ISREF functions in Google Sheets with the various other Google Sheets formulas available to create more powerful spreadsheets for your tasks.

Stay notified of new guides like this by subscribing to our newsletter!

Get emails from us about Google Sheets.

Our goal this year is to create lots of rich, bite-sized tutorials for Google Sheets users like you. If you liked this one, you'd love what we are working on! Readers receive ✨ early access ✨ to new content.

Subscribe