Excel INDIRECT(): How to Build Dynamic References in Your Formulas

The INDIRECT() function is one of Excel’s most powerful yet often misunderstood tools. Unlike functions that perform calculations directly, INDIRECT() converts text strings into live cell references. This allows users to create dynamic and flexible formulas that adapt to variable inputs. Whether you're building a summary dashboard, referencing different sheets based on user selections, or dynamically changing cell references, INDIRECT() can be a game-changer.

In this guide, I will show you how to use the INDIRECT() function for cell referencing and troubleshoot common problems, including when to avoid using the function. If you are unfamiliar with some basics, I recommend you take our Introduction to Excel course to build a solid foundation. 

What the INDIRECT() Function Does

The INDIRECT() function in Excel retrieves the value of a cell or range based on a reference provided as a text string. Instead of hardcoding a cell address like "A1" into a formula, INDIRECT() lets you dynamically define that address using text, which Excel interprets as a live reference.

The syntax of the INDIRECT() function is shown below:

INDIRECT(ref_text, [a1])

Where:

• ref_text (required): A text string representing a valid cell or range reference. This can refer to a cell like "A1", a named range, or even a reference on another sheet such as "Sheet2!B5".

• [a1]: This is an optional argument that specifies the type of reference in ref_text. If a1 is TRUE or omitted, ref_text is interpreted as an A1-style reference. If a1 is FALSE, ref_text is treated as an R1C1-style reference.

How to Use the INDIRECT() Function

The following example shows how the INDIRECT() function dynamically converts text into cell references.

Suppose cell C2 contains the text "A2" and cell A2 contains the value 100. The function below will also return 100 because Excel reads the content of C2 and INDIRECT() treats it as a reference.

=INDIRECT(C2)

Using INDIRECT() to reference based on another cell in Excel. Image by Author.

If cell A5 contains the number 5, you can build a dynamic reference using the formula below:

=INDIRECT("B"&A5)

This formula combines "B" with the value in A5 (6), forming "B6". INDIRECT() then returns the value in cell B6, which is "Product A".

Using INDIRECT() with & for dynamic referencing in Excel. Image by Author.

Dynamic Uses of INDIRECT() in Excel

The true strength of the INDIRECT() function is its ability to build dynamic references. This makes it ideal for advanced modeling, dashboards, and user-driven reports. Below are three key use cases that showcase its flexibility.

Reference other worksheets

You can use INDIRECT() to dynamically pull data from different sheets based on a cell’s input. For example, if you have cell A1 containing the name of a sheet, like "Sales". You want to pull the value from cell B2 on that sheet.

The formula below constructs a reference like 'Sales'!B2 and returns the value from that cell.

=INDIRECT("'" & A1 & "'!B2")

Using INDIRECT() to reference other worksheets indirectly in Excel. Image by Author.

Always enclose sheet names in single quotes using ' if there's any chance they contain spaces or special characters, like 'Sales 2023'!A1.

Reference named ranges

You can also use the INDIRECT() function to reference named ranges dynamically, which is powerful when combined with dropdowns or user input.

Assume you have named ranges, Sales_2023 and Sales_2023, where cell “A1” contains "Sales_2023"

Naming cell ranges in Excel. Image by Author.

The formula below will sum the values in the named range Sales_2023, even though it doesn’t reference it directly. This method is great for user-driven dashboards or models with multiple scenarios.

=SUM(INDIRECT(A1))

Using INDIRECT() to reference named ranges in Excel. Image by Author.

Fixed references that don’t change

Normally, if you insert or delete rows or columns, cell references in formulas adjust automatically. But the INDIRECT() function creates absolute references that don’t shift.

For example, if you have cell “A1” containing 100, the formula =A1 and =INDIRECT("A1") will both return 100. However, if you shift the value

Direct cell reference in Excel. Image by Author.

Using INDIRECT() for cell referencing in Excel. Image by Author.

However, if you insert a new line above row 1, D3 (was B2) now shows =A3 since the value shifts with the row. The cell E3 with the INDIRECT() function still shows the original A2, which may now be empty or have a different value (UID).

Using INDIRECT() for fixed references in Excel. Image by Author.

Advanced Examples and Use Cases

Once you're comfortable with the basics, the INDIRECT() function shines in more advanced Excel scenarios, especially when paired with functions like VLOOKUP(), ROW(), and dynamic arrays. These combinations unlock even more dynamic and flexible models.

Use INDIRECT() with VLOOKUP()

You can combine INDIRECT() with VLOOKUP() to dynamically change the lookup table based on user input.

Assume you have two tables, Prices_2022 and Prices_2023. In separate cells, G1: Prices_2022 and G2: Apple`. The formula below dynamically searches for "Apple" in the selected year’s table based on the value in A1 and returns its price.

=VLOOKUP(G2, INDIRECT(G1), 2, FALSE)

Using INDIRECT() with VLOOKUP() in Excel. Image by Author.

Check out our VLOOKUP() from Another Sheet: A How-to in Excel tutorial to learn more about searching and retrieving data from different Excel sheets.

INDIRECT() with R1C1 referencing

INDIRECT() can also use R1C1 notation, which is especially helpful when constructing formulas dynamically in macros or referencing positions programmatically.

For example, the formula below returns the value in row 2, column 3 ( cell C2). The second argument (FALSE) tells Excel you use the R1C1 style, not the A1.

=INDIRECT("R2C3", FALSE)

Using INDIRECT() with R1C1 in Excel. Image by Author.

The following formula includes the dynamic use of ROW() and COLUMN(). The formula will always return the value from column 3 on the same row where the formula is written.

=INDIRECT("R" & ROW() & "C3", FALSE)

Using INDIRECT() with ROW() and COLUMN() in Excel. Image by Author.

INDIRECT() and dynamic arrays

Before Excel introduced dynamic arrays, INDIRECT() was a common method to generate array-like behavior.

For example, the formula below returns an array: {1; 2; 3; 4; 5}. This simulates an array from 1 to 5 by referencing rows indirectly.

=ROW(INDIRECT("1:5"))

Using INDIRECT() with ROW() for dynamic arrays in Excel. Image by Author.

The modern alternative method is using the SEQUENCE() function.

=SEQUENCE(5)

Using SEQUENCE() for dynamic arrays in Excel. Image by Author.

Using INDIRECT() for dynamic arrays still works, but it's considered less efficient and less readable than SEQUENCE(), FILTER(), and other dynamic array functions available in modern Excel (Office 365 and Excel 2021+).

Cautions and Performance Concerns

The INDIRECT() function has some important limitations, especially when used in large or complex Excel workbooks. These issues include the following:

• Volatile function (recalculation overload): INDIRECT() is a volatile function, meaning it recalculates whenever any change is made anywhere in the workbook, even if the change has nothing to do with the formula. This can cause slower performance.

• Debugging can be challenging: Since INDIRECT() builds references from text, Excel can't easily trace them like it can with standard cell references. That makes it hard to audit formulas with trace precedents/dependents, or catch broken references when something changes.

• Consider modern alternatives: When possible, it's often better to use non-volatile functions that offer better performance and clarity. These methods include INDEX() for dynamic cell lookups, CHOOSE() to select from predefined ranges or options dynamically, and SEQUENCE() for generating dynamic numeric arrays.

When to avoid using INDIRECT()

While INDIRECT() offers powerful flexibility, it’s not always the best choice. It often introduces unnecessary complexity, performance issues, or maintainability problems. Below are some situations you should avoid and the best alternative methods.

• Dynamic cell references within the same sheet: Use INDEX() instead of INDIRECT() for better performance and traceability.

• Dynamic arrays or number sequences: Avoid =ROW(INDIRECT("1:10")). Instead, use =SEQUENCE(10), which is faster, cleaner, and non-volatile.

• When traceability is important: Formulas using INDIRECT() can’t be traced with “Trace Precedents” or “Evaluate Formula” effectively.

• Referencing a static cell or range: Don’t use INDIRECT("A1") if you can just write =A1.

• Heavy use in large spreadsheets: Since INDIRECT() is volatile, it recalculates constantly and can slow down performance significantly.

INDIRECT() alternatives

The following table summarizes the best alternatives to INDIRECT().

Key Takeaways

INDIRECT() turns text strings into live references, making it a powerful tool for building dynamic and flexible spreadsheets. It is useful for referencing different sheets, named ranges, or fixed cells that shouldn't shift with changes. However, it is a volatile function that recalculates frequently and can slow down large workbooks. The function is also harder to trace and debug than traditional functions. Use INDIRECT() selectively, and consider safer, more efficient alternatives.

I recommend taking our Data Analysis in Excel course to learn about insights in B2B data and the Excel Fundamentals skill track to learn more about the functions available in the latest Excel versions. Our Advanced Excel Functions is a perfect course if you are exploring advanced referencing and lookups.