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.
ISREFfunction 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:
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
ISREFfunction. 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
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:
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
- In this example, we’ll be using
INDIRECTto determine whether certain strings are valid cell references. As seen below, we begin by selecting a cell to place our formula.
- In the next step, we just have to type the equal sign ‘=’, followed by ‘ISREF(‘.
- A pop-up box may appear with information on the
ISREFfunction. We can click on the arrow on the top-right-hand corner of the box to minimize it.
- The next step is to type in our argument. For this example, our arguments must first pass through the
INDIRECTfunction, which converts the string to an actual cell reference.
- 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.
- Finally, we can drag down the formula in cell B2 to fill in the rest of the column!
Frequently Asked Questions (FAQ)
- Does the ISREF function take different sheets into consideration?
ISREFfunction 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.
- Does the ISREF function consider the INDEX and OFFSET functions as cell references?
As long as the cells referenced by
ISREFfunction 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
ISREFfunction 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!