What Is ISREF Function In Google Sheets?
The ISREF Function in Google Sheets allows users to determine if a given cell reference is valid or not and returns TRUE if the provided input is a valid cell reference and FALSE otherwise.
The Google Sheets ISREF Function is useful in large datasets that require extensive calculations or complex formulas, as it helps identify potential errors or unpredicted results and helps in identifying and removing non-reference values.
For example, we will enter the ISREF Google Sheets Function in the cell and check the result.
Select cell A2, enter the formula =ISREF(A2)and press “Enter” as shown below.
The result is “True” because the formula is referencing to the cell A2. Column B is for our reference.
Key Takeaways
- The ISREF function in Google Sheets checks the data type of a reference, whether a given cell or range of cells contains a valid reference. It returns the Boolean value “TRUE” if the input is indeed a reference and “FALSE” if it isn’t.
- To get alternate results or expressions, apart from the default TRUE or FALSE, we can use the IF function with the ISREF function and provide the needed expressions that we would like to see as an output.
- It is especially valuable in complex formulas and macros where references play an integral role, ensuring accuracy and preventing potential errors.
- To distinguish between references and other types of data, such as numbers or text, the ISREF function significantly enhances productivity and efficiency for professionals who rely heavily on Google Sheets for data analysis and decision-making processes.
- We can use the function along with other functions such as OFFSET(), INDIRECT(), VLOOKUP(), INDEX(), MATCH(), etc.
Syntax
The syntax of the ISREF Google Sheets formula is,
The one and only mandatory argument of the ISREF formula in Google Sheets is,
- value: It refers to the specific value or expression that is tested.
How To Use ISREF Function In Google Sheets?
We can use the ISREF Function in Google Sheets in two ways, as follows:
- Access from the Google Sheets ribbon.
- Enter the formula in the worksheet manually.
Method #1 – Access from the Google Sheets ribbon
Step 1: Choose an empty cell for the output – select the “Insert” tab – click the “Function” option right arrow – click the “Info” option right arrow – select the “ISREF” function, as shown below.
Step 2: The “ISREF” formula appears, as shown below. Enter the argument as cell value or cell reference and press “Enter”.
Method #2 – Enter the formula in the worksheet manually
Step 1: Select an empty cell for the output.
Step 2: Type =ISREF( in the cell, as shown below. [Alternatively, type =I or =IS and double-click the ISREF from the Google Sheets suggestions.]
Step 3: Enter the argument as cell value or cell reference.
Step 4: Close the brackets and press “Enter”.
Examples
Let us consider some ISREF Function in Google Sheets examples in combination with other functions such as OFFSET(), VLOOKUP(), INDIRECT(), etc.
Example #1 – ISREF with INDIRECT
Consider the dataset given below that consists of the worksheet names used in the current workbook and some that do not exist. We will use the ISREF with INDIRECT to check for valid cell reference.
[Note: INDIRECT function returns a cell reference specified by a string.]
The steps to use ISREF with INDIRECT to find the desired output are,
Step 1: Select cell B2 and enter the formula =ISREF(INDIRECT(A2&“!A1”)), as shown below.
Step 2: Press “Enter” and drag the formula from cell B2 to B10 using the fill handle to get the following results.
The output is shown above, that the worksheets available in the current open workbook is TRUE and the others that does not exists are returned as FALSE.
Example #2 – ISREF With OFFSET
Consider the dataset of Example #1 again and we will use the ISREF with OFFSET to check for valid cell reference.
[Note: OFFSET function provides a reference to a range that is constructed using five specific inputs that are (1) a starting point, (2) a row offset, (3) a column offset, (4) a height measured in rows, and (5) a width measured in columns.]
The steps to use ISREF with OFFSET to find the desired output are,
Step 1: Select cell B2 and enter the formula =ISREF(OFFSET(A2,2,1)), as shown below.
Step 2: Press “Enter” and drag the formula from cell B2 to B10 using the fill handle to get the following results.
The output shown above is different from the INDIRECT+ISREF results we got in Example 1. The OFFSET looks for the row and column index number provided and checks if the cell references is available or not. And that is the reason we got all the result as TRUE, because, regardless of the first cell argument, the row 2 and column 1 reference is found according to the formula.
Example #3 – ISREF with VLOOKUP
The dataset given below consists of student names and their scores in exams. We will use the ISREF with VLOOKUP to check find the score of Casey with and without ISREF.
[Note: VLOOKUP function returns corresponding values of the search_key from the dataset.]
The steps to use ISREF with VLOOKUP to find the desired output are,
Step 1: First, let’s find VLOOKUP. So, select cell E2, enter formula =VLOOKUP(D2,A2:B15,2,0) and press “Enter”, to get the following result.
Step 2: For ISREF+VLOOKUP, select cell E7, enter formula =ISREF(VLOOKUP(D2,A2:B15,2,0)) and press “Enter”, to get the following result that the reference exists.
Step 3: We can change the search_key value to recheck the formula. So, change the value from “Casey” to “Sheeba” and we can see the result, as shown below.
Example #4
In the dataset given below, we will enter some formulas with various references. We will validate the results to see the difference when there are various types of entries.
The steps to enter the formula with valid an invalid reference, to derive the desired results are,
- Select cell A2, enter the formula =ISREF(A1) and press “Enter”.
- Select cell A3, enter the formula =ISREF(A1*A2) and press “Enter”.
- Select cell A4, enter the formula =ISREF(A1:F4) and press “Enter”.
- Select cell A5, enter the formula =ISREF(“Data”) and press “Enter”.
- Select cell A6, enter the formula =ISREF(A1:A2) and press “Enter”.
- Select cell A7, enter the formula =ISREF(4+6) and press “Enter”.
- Select cell A8, enter the formula =ISREF(A2+A3) and press “Enter”.
The output is shown above. Column B is for our reference to understand the formulas used in column A.
Important Things To Note
- The ISREF function contributes to increased accuracy and efficiency in spreadsheet analysis and ensures smooth data retrieval by preventing errors caused by invalid references.
- Its versatility lies in its ability to work with both absolute and relative references in Google Sheets or MS Excel, making it an invaluable tool for professionals who completely rely on Google Sheets or MS Excel, for data analysis, financial modelling, or project management tasks.
- Using the ISREF function, professionals can save time troubleshooting errors and ensure the accuracy of their spreadsheets.
Frequently Asked Questions (FAQs)
The ISREF function in Google Sheets might not work due to the following reasons,
a. The function name is incorrectly entered.
b. We are trying to retrieve other info against the inbuilt purpose of the function.
c. The cell value is entered without double-quotes, treating it as a cell reference.
We often forget in which category a function falls, here, the “ISREF” function. Then, we can insert the function as follows:
Choose an empty cell -select the “Insert” tab – click the “Function” option right arrow – click the “All” option right arrow – select the “ISREF” function, as shown below.
However, as always, entering the function manually is the best way to avoid confusion.
Alternatively, we can find the Functions icon to insert the ISREF function in Google Sheets by following the path shown below.
Choose an empty cell – click the “More” option represented by the three vertical dots at the end of the toolbar, as shown below.
A list of icons appears when we click the “More” option. Here, click the “Functions” icon, as shown below.
Here, click the “Functions” option – click the “All” option right arrow – select the “ISREF” function, as shown below.
A few limitations of the ISREF function in Google Sheets are as follows:
• It can only be used with direct cell references or named ranges. It will not work with structured references or array formulas.
• It cannot be used to test for the existence of external file links or hyperlinks.
• It does not support wildcard characters like asterisks (*) or question marks (?).
Download Template
This article must be helpful to understand the ISREF Function In Google Sheets, with its formula and examples. You can download the template here to use it instantly.
Recommended Articles
Guide to What Is ISREF Function In Google Sheets. We learn its syntax and how to use it to find if a reference is valid with examples. You can learn more from the following articles. –
Leave a Reply