How to Use the Excel INDIRECT Function with Named Range (2 Examples)

Named Ranges are a powerful feature in Excel that allows you to assign a name to a range of cells, making it easier to reference and use that range throughout your workbook. However, working with Named Ranges dynamically can be challenging. That’s where the INDIRECT function comes in handy.

Overview image of using the INDIRECT function with named ranges

Introduction to the INDIRECT Function in Excel

Syntax of the INDIRECT function

Syntax of INDIRECT Function

The INDIRECT function references a range based on a text string. Its syntax is as follows:

=INDIRECT(ref_text, [a1])

ref_text (required): A cell reference that points to a cell containing a text string. You can use a cell reference, a named range, or a formula that yields a valid cell reference.

[a1] (optional): A logical value that specifies the type of reference to use. If set to TRUE or omitted, the function uses an A1-style reference. If set to FALSE, it uses R1C1-style reference. For most cases, stick to A1 references.

Dataset Overview

Suppose we have data on Sales Representatives and the price of Fruits, Vegetables, and Meat in columns B, C, D, and E.

We’ve defined the following Named Ranges:

Fruits: C5:C10
Vegetables: D5:D10
Meat: E5:E10

Example 1 – Applying INDIRECT Function with Named Range for Single Worksheet in Excel

To simplify formulas and make them more manageable, we’ll use the SUM, AVERAGE, MAX, and MIN functions along with INDIRECT.

Here’s how:

In cell H5, input the following formula:

=SUM(INDIRECT($H$4))

- The $H$4 reference represents a cell where the named range is stored as text.
- The INDIRECT function takes this text string and returns a reference to the named range.
- The SUM function calculates the sum of values in the referenced cells.

Similarly, in cells H6, H7, and H8, enter the following formulas:

=AVERAGE(INDIRECT($H$4))

=MAX(INDIRECT($H$4))

=MIN(INDIRECT($H$4))

Short Explanation of Formula

=SUM(INDIRECT($H$4))

The INDIRECT function refers to a cell or range of cells indirectly. The $H$4 reference in the formula represents a cell address where a named range is stored as text. The INDIRECT function takes this text string as its argument and returns a reference to the cell or range of cells named in that text string.

The returned reference is then passed as an argument to the SUM function, which calculates the sum of the values in the referenced cells.

Note: Use of the dollar sign ($) in the reference to cell H4 means that the reference is absolute and will not change if the formula is copied to another cell.

Example 2 – Using INDIRECT Function with Named Range for Different Worksheets

Suppose you have data spread across various worksheets, and you want to reference cell ranges dynamically. Here’s how to achieve that using the INDIRECT function and named ranges:

In cell H5, input the following formula:

=INDIRECT("'" & $C$4 & "'!" & C5)

- The $C$4 reference represents a cell where the named range is stored as text.
- The INDIRECT function combines the sheet name (from cell C4) and the cell reference (C5) to create a dynamic reference.
- You’ll get the value from the specified cell (E5 in this case), which is 29.

Short Explanation of Formula

The formula =INDIRECT(“‘” & $C$4 & “‘!” & C5) references a cell in another sheet within the same workbook.
The concatenation operator (&) combines text strings and cell references.

Note: The dollar sign ($) in the reference to cell C4 ensures it remains absolute when copied to other cells.

Troubleshooting – When Excel INDIRECT Function with Named Range Is Not Working

If you encounter #REF errors (as seen in cells H5 to H8), it’s likely due to an incorrect named range.

For example, if “Food” is not a valid named range, the formula won’t work.

Ensure you use the correct named range (e.g., “Meat” in cell H4).

Using INDIRECT Formula with R1C1 References in Excel

R1C1 reference style uses rows followed by columns (e.g., R7C1 refers to cell A7).

Let’s see how INDIRECT handles R1C1 references:

Select cell H5 and apply the formula:

=INDIRECT(G5,FALSE)

- The value in cell G5 (R7C2) points to row 7 and column 2.
- The output will be Stephen.

Things to Remember

If the INDIRECT formula refers to another worksheet, that workbook must be open; otherwise, it will return a #REF! error.
Excel allows both A1 and R1C1 reference styles, but you can’t use them simultaneously on a single sheet.
INDIRECT is volatile, recalculating whenever the workbook opens or a worksheet calculation occurs. Use it carefully with large datasets.

Frequently Asked Questions

What happens if the referred sheet or workbook is closed?

The other workbook or spreadsheet must be open; otherwise, INDIRECT will return a #REF! error.

Can you use INDIRECT for a range?

Yes, INDIRECT can indirectly reference cells, ranges, other sheets, or workbooks dynamically.