The Excel ISREF function lets you check whether the given reference (to a cell or range) is valid or not. It returns TRUE or FALSE as the output:
- TRUE when the given value is a reference
- FALSE when it is not a reference
It helps us prevent errors in calculations and data processing. The function is mainly used to check the worksheets within the workbook.
For example, suppose you are creating a small dashboard for students to display their scores in an exam that you conduct monthly. The dashboard is simple as follows:
Note that the above image contains an error message FALSE because we haven’t entered any data (roll number) in cell A2.
We will assume that the roll numbers are based on the cell corresponding to the student in the main table, as shown below:
In our dashboard, we can use the following formula to know if the roll number (or cell reference corresponding to the given student) entered in cell A2 is valid.
=ISREF(INDIRECT(A2))Where,
- A2 is the cell reference where students will enter their roll number
- INDIRECT(A2) is the formula to convert the text (roll number) entered into a cell reference
Now, if we type A6, we will get the scores for Theodore as shown in the following GIF.
Next, we will not get any data unless we type a valid roll number (or cell reference corresponding to the given student). For example, I typed the student’s name directly instead of his/her roll number, as shown below.
To fetch the name and roll number from the main table, we use the VLOOKUP function in Excel. Similarly, to highlight the valid roll number, we use the Conditional Formatting feature in Excel.
Syntax
The ISREF is a simple function with the following syntax. It is mostly combined with the INDIRECT function in Excel to check whether the given cell contains a valid reference.
=ISREF(value)Where the value argument needs to be replaced with a cell reference or a direct value.
Important Notes:
- The function cannot check the contents of the given cell. For example, consider that cell A2 has a reference that needs to be tested. If you directly specify the cell reference in the formula: =ISREF(A2). It always returns true as the value specified is a valid cell reference.
- You need to use the ISREF with the INDIRECT function to evaluate the contents of the given cell. For example, the formula =ISREF(INDIRECT(A2) checks the contents of cell A2.
- It is mainly used to avoid #REF! errors.
How to use the ISREF Function in Excel
Download the example spreadsheet used to demonstrate the ISREF function in the upcoming section. Practice is the key to success!
Example 1: ISREF Function Basics
Suppose you own an online store and have created a real-time Excel stock tracker. It includes a central dashboard empowered by various sheets within the same workbook. Those worksheets are based on product categories and maintained by the category manager. They include stock of individual products within the respective category.
Refer to the following image:
It is our central dashboard where the stock of all products is displayed. Before fetching the stock from the respective category worksheet, we need to verify that the cell references mentioned in column C are valid.
For this purpose, we can use Excel’s ISREF and INDIRECT functions.
Here are the steps:
- Select the cell D2
- Type =ISREF
- Choose the first option from the popup
- Type INDIRECT
(As the INDIRECT is a built-in function in Excel, you will see the following popup) - Double-click the INDIRECT command from the list
- Specify the cell reference C2
- Complete the bracket for the INDIRECT function using )
- Complete the bracket for the ISREF function using )
- Hit the Enter key
Our formula would be as follows:
=ISREF(INDIRECT(C2))Where,
- C2 holds the first reference to be tested
- INDIRECT(C2) is the formula to convert the text value available in the cell C2 into a cell reference
The results are displayed in the following GIF.
Further, we can use the IF function in Excel to fetch the stock from the respective category worksheet.
Our formula would be as follows:
=IF(ISREF(INDIRECT(C2)), INDIRECT(C2), "Invalid Reference"))Where,
- ISREF(INDIRECT(C2)) is the formula for identifying whether cell C2 contains a valid reference.
- INDIRECT(C2) is the formula that converts the text value within the cell C2. If the cell C2 holds a valid cell reference, the formula will return the value available within that cell reference.
- Invalid Reference is the desired output if the cell C2 contains no valid reference.
The results are displayed in the following GIF.
When to use the Excel ISREF function?
The ISREF is a prebuilt function in Excel, categorised under Excel Information functions. It is particularly helpful when you are dealing with references. It helps you check whether the given value is a valid cell reference and retrieve that cell’s data.
Here are a few scenarios where you can use the ISREF function:
- Avoid #REF! error when using the INDIRECT function to convert text into a reference to display the data.
- Validate the user input across the shared files.
- Check whether the given formula contains valid cell references and points towards the desired cell or range.
- Verify if the sheet exists within the given workbook.
Takeaway
ISREF is a rarely used function in Excel. It is a part of the functions used to validate the contents of the given cells.
I hope this article taught you all the bells and whistles of the ISREF function. Please comment below if you are stuck or encounter any particular error while using it. I will answer your questions as soon as possible.
Additional Resources:
- Learn All Excel Information Functions (With Examples)
- IF Function in Excel
- INDIRECT Function in Excel
- ISFORMULA Function in Excel
- IFNA Function in Excel
- ISERROR Function in Excel
- ISTEXT Function in Excel
- IFERROR Function in Excel
- Guide to Conditional Formatting in Excel
Get an Office 365 Subscription to Access all the powerful Functions and Tools in Excel.