ISREF Function

The ISREF function is an inbuilt Excel Information function. It accepts a value as input and returns TRUE if the specified value refers to a valid reference. Otherwise, the function returns FALSE.

Users can utilize the ISREF function while conditionally formatting cells containing values referring to cell references or ranges. The function also helps evaluate Excel data based on the cell references’ validity.

For example, the dataset below contains the built-in ISREF function input descriptions.

The aim is to apply the built-in ISREF function in the column B cells according to the input values specified in the corresponding column A cells to view the desired output.

In the above Excel’s ISREF function example, we enter the ISREF() in each target cell in column B and the function input is based on the corresponding description in column A.

When the argument value supplied to the ISREF function refers to a valid cell reference or range, as in the case of the target cells B2 and B3, then the function returns TRUE.

Conversely, if Excel’s ISREF function accepts a value such as text or a number, as in the case of the target cells B4 and B5, the function returns FALSE.

Furthermore, if the ISREF() accepts a function or formula returning a valid reference, the function output will be TRUE. But, if the function argument value is a function or formula returning a value other than a valid reference, such as in the case of the target cell B6, the ISREF() returns FALSE.

• The ISREF function accepts a value and returns TRUE if the specified value refers to a valid cell or range reference. Otherwise, the function returns logical FALSE.
• Users can utilize the ISREF Excel function for checking cells containing valid references in massive datasets.
• The ISREF Excel function accepts one mandatory argument, value.
• The ISREF Excel function works well as a standalone function. However, sometimes, we must combine it with other related inbuilt functions, such as INDIRECT and IF, to achieve the correct output.

The syntax of the ISREF function for both Mac and PC is the following:

Where,

• value: The value we aim to check if it refers to a valid reference using the ISREF function for both Mac and PC.

The ISREF function argument is compulsory, and it can be a value or expression.

We can apply the ISREF function in Excel using the following methods:

1. Access the function from the Excel ribbon.
2. Enter the function into the worksheet manually.

Method #1 – Access The Function From The Excel Ribbon

Choose a cell to show the output - The Formulas tab - The More Functions down arrow - The Information function group right arrow - ISREF.

The Function Arguments window will open. Here, we can enter the ISREF() argument value in the Value field.

Finally, click OK in the Function Arguments window to obtain the ISREF() output as a logical value TRUE or FALSE in the chosen cell.

Method #2 - Enter The Function Into The Worksheet Manually

1. Select a target cell to display the output.
2. Type =ISREF( in the cell.
3. Enter the argument as a value or formula and close the brackets.
4. Press Enter to view the ISREF() output in the target cell.

Check out the following ISREF function examples to utilize the function effectively.

Example #1

The source dataset contains the ISREF function input descriptions in column A.

The task is to apply the ISREF function in the column B cells, with the argument values being according to the corresponding input descriptions in column A.

Then, the steps are as follows:

Step 1: Choose cell B2, enter the ISREF(), and press Enter.

=ISREF(K132)

The ISREF() input is a cell reference K132. So, the function checks if the input value is a valid cell reference, which is true in this case. Hence, it returns TRUE as the output.

Step 2: Choose cell B3, enter the ISREF(), and press Enter.

=ISREF(YZB11)

The ISREF() input is a cell reference YZB11. The formula evaluates the cell reference to the #NAME? error value since the specified cell reference is invalid. So, the function checks if the error value is a valid cell reference, which is not true. Hence, it returns FALSE as the output.

Step 3: Choose cell B4, enter the ISREF(), and press Enter.

=ISREF(Sample_Data)

The ISREF() input is a named range, Sample_Data, in the worksheet Sheet2. The formula evaluates the named range to the corresponding cell range in Sheet2. Thus, the function checks if the input value refers to a valid range reference, which is true in this case. Hence, it returns TRUE as the output.

Step 4: Choose cell B5, enter the ISREF(), and press Enter.

=ISREF(Sheet2!#REF!)

The ISREF() input value is a reference to a deleted cell in the worksheet Sheet2, which the formula evaluates to the #REF! error value. So, the function checks if the error value is a valid cell reference, which is not true. Hence, it returns FALSE as the output.

Step 5: Choose cell B6, enter the ISREF(), and press Enter.

=ISREF(10+55)

First, the addition formula adds two numbers, 10 and 55, and returns the sum as 65. Next, the ISREF() checks if the addition formula output is a valid reference or a value that refers to a reference. But since the addition formula output is a number, the ISREF() output is FALSE.

Step 6: Choose cell B7, enter the ISREF(), and press Enter.

=ISREF(INDEX(J:J,23))

First, the Excel INDEX function returns the absolute reference to the cell at the intersection of column J and row 23, $J$23. Next, the ISREF() accepts the INDEX() output as the argument value. So, the function checks if the input value refers to a valid reference, which is true in this case. Hence, it returns TRUE as the output.

Example #2 - Using ISREF With INDIRECT

We can use the ISREF function with the INDIRECT Excel function, as explained below.

Column B in the source dataset shows the descriptions, explaining how to supply the INDIRECT() as the argument value to the ISREF() in each corresponding column C target cell.

Let us see the formulas to apply in each column C target cell to obtain the required outcome.

Step 1: Choose cell C2, enter the ISREF() containing the INDIRECT(), and press Enter.

=ISREF(INDIRECT("d50"))

First, the INDIRECT() returns the absolute reference to the specified cell D50, $D$50. Next, the ISREF() checks if the INDIRECT() output is a valid reference, which is true in this case. Hence, the ISREF() output is TRUE.

Step 2: Choose cell C3, enter the ISREF() containing the INDIRECT(), and press Enter.

=ISREF(INDIRECT("Sheet2!"&"E10"))

First, the INDIRECT() returns the absolute reference to the specified cell E10 in the worksheet Sheet2, Sheet2!$E$10. Next, the ISREF() checks if the INDIRECT() output is a valid cell reference, which is true in this case. Hence, the ISREF() returns TRUE.

Step 3: Choose cell C4, enter the ISREF() containing the INDIRECT(), and press Enter.

=ISREF(INDIRECT(A4))

First, the INDIRECT() accepts the cell reference A4 as the input. Next, the formula evaluates the INDIRECT() input value to the cell A4 value, "XET25". After that, the INDIRECT() returns the absolute reference, $XET$25, based on the input "XET25". Next, the ISREF() checks if the INDIRECT() output is a valid cell reference, which is true in this case. Hence, the ISREF() returns TRUE.

Example #3 - Using ISREF With OFFSET

We can use the ISREF function with the OFFSET Excel function, as explained below.

Column A in the source dataset shows the descriptions, explaining how to supply the OFFSET() as the argument value to the ISREF() in each corresponding column B target cell.

Let us see the formulas to apply in each column B target cell to obtain the required outcome.

Step 1: Choose cell B2, enter the ISREF() containing the OFFSET(), and press Enter.

=ISREF(OFFSET(C23,5,10))

First, the OFFSET() returns the absolute reference to cell M28, which is 5 rows and 10 columns from the reference point cell C23, $M$28. Next, the ISREF() checks if the OFFSET() output is a valid reference, which is true in this case. Hence, the ISREF() output is TRUE.

Step 2: Choose cell B3, enter the ISREF() containing the OFFSET(), and press Enter.

=ISREF(OFFSET(XFD1048576,0,1))

First, the OFFSET() is expected to return the absolute reference to the cell, which is in the same row as the reference point cell XFD1048576 but in the next column on its right. However, since the reference point is the last cell in a worksheet, the OFFSET() returns the #REF! error. Next, the ISREF() checks if the OFFSET() output is a valid reference, which is not true in this case. Hence, the ISREF() output is FALSE.

Step 3: Choose cell B4, enter the ISREF() containing the OFFSET(), and press Enter.

=ISREF(OFFSET(S60,-2,-4))

First, the OFFSET() returns the absolute reference to cell O58, which is 2 rows and 4 columns before the reference point cell S60, $O$58. Next, the ISREF() checks if the OFFSET() output is a valid reference, which is true in this case. Hence, the ISREF() output is TRUE.

• Ensure the ISREF function argument value is a valid value or an error-free formula. Otherwise, the function may return an incorrect output.
• The ISREF Excel function checks for a cell or range reference, not a cell value.
• The ISREF Excel function does not return an error value such as #NAME? and #REF!, except when the function name is incorrect. Its output is always a logical TRUE or FALSE.

This article must be helpful to understand the ISREF Function, with its formula and examples. You can download the template here to use it instantly.

This has been a guide to ISREF Function in Excel. Here we learn the ISREF excel formula & how to use it with examples and points to remember. You can learn more from the following articles –

• Excel Reference to Another Sheet
• Mixed References in Excel
• 3-D Reference in Excel