INDIRECT Excel Function: Examples, Uses, and Limits

In Excel, INDIRECT converts text into a live cell or range reference; if A2 contains B5, =INDIRECT(A2) returns the value in B5. It is useful when a formula must choose a cell, range, or worksheet from text, but it has important limits—especially its volatility and its inability to retrieve data from a closed external workbook.

What INDIRECT Does

INDIRECT is a reference function available in current Excel versions, including Microsoft 365 for Windows. Its syntax is INDIRECT(ref_text, [a1]). The required ref_text argument is text that Excel can interpret as a cell or range reference. The optional a1 argument determines how Excel reads the reference: TRUE, or an omitted argument, means A1-style notation; FALSE means R1C1-style notation.

The key distinction is that INDIRECT does not look up a value from text in the way a search function does. Instead, it turns the text into a reference, and Excel then evaluates that reference. That can make a formula flexible, but it also means the text must form a valid reference in the current workbook.

A1-style references look like A1, D12, or B2:B20.
R1C1-style references use row and column numbers, such as R3C2 for cell B3.
Worksheet references can include a sheet name and an exclamation mark, such as Sales!B2.

Basic Cell-Reference Examples

Suppose A2 contains the text B5 and B5 contains 42. With that layout, =INDIRECT(A2) returns 42. Changing A2 to C5 makes the formula refer to C5 instead. This is the simplest way to see the difference between a text label and a reference: INDIRECT interprets the label as an address.

You can also build an address by joining text. With the row number 5 in A2 and the column letter B in B2, =INDIRECT(B2&A2) creates the reference B5 and returns the value in B5. This technique can be handy when a row or column is selected from another cell, although it becomes harder to maintain if the address is assembled from many pieces.

To refer to a range, put the range address in a cell. If A2 contains B2:B5, then =SUM(INDIRECT(A2)) adds the values in B2 through B5. For example, if those cells contain 3, 4, 5, and 6, the result is 18. The outer SUM performs the addition; INDIRECT supplies the range reference.

Choosing a Worksheet from a Cell

A common use is selecting a worksheet dynamically. Assume A2 contains the worksheet name January, the sheet January has 125 in B3, and the sheet name contains no apostrophe. The formula =INDIRECT("'"&A2&"'!B3") returns 125. The single quotation marks around the sheet name allow names with spaces, such as Quarterly Data, to work as well.

The same idea can select a range to summarize. Suppose A2 contains January and each monthly sheet has values in B2:B13. With those assumptions, =SUM(INDIRECT("'"&A2&"'!B2:B13")) totals that range on the named sheet. If the values in January!B2:B13 add to 780, the formula returns 780. Entering February in A2 redirects the calculation to the February sheet, provided that sheet and range exist.

Be careful if a worksheet name itself contains an apostrophe. Excel represents an apostrophe inside a quoted sheet name by doubling it in the reference text. A formula that builds sheet references from arbitrary names therefore needs to account for that escaping; otherwise, the resulting text may not be a valid reference.

Using INDIRECT with Data Validation Lists

INDIRECT is often used for dependent drop-down lists: a choice in one list determines the source list for another. For example, a workbook might have a first list of regions and a second list of cities for the selected region. One traditional setup gives each city list a defined name matching its region, then uses the selected region to identify the matching named range.

Create the source lists and define a name for each dependent list. Names cannot contain spaces, so the naming scheme must be compatible with the region labels.
Set the first drop-down to use the region list through Data > Data Validation, then choose List on the Settings tab.
Select the cell for the dependent drop-down, open Data > Data Validation, and choose List on the Settings tab.
If the selected region is in A2 and the defined name exactly matches its text, enter =INDIRECT(A2) in the Source box.

For instance, if A2 contains West and a defined name West refers to the West-city list, the second validation list displays that list. If labels contain spaces or punctuation, the defined names may need a consistent naming convention and a formula to translate the label into the name. This approach is useful in older workbooks, but it is not the only way to build dependent lists; structured tables and newer formulas may be easier to expand and maintain.

R1C1 References and the Optional Argument

By default, INDIRECT interprets text as an A1-style address. For example, with 17 in cell B3, =INDIRECT("B3") returns 17. You can state the default explicitly with TRUE as the second argument, but it is generally unnecessary.

To use R1C1 notation, set the optional argument to FALSE. With 29 in cell C2, =INDIRECT("R2C3",FALSE) refers to row 2, column 3 and returns 29. This can be useful when constructing references numerically, since column 3 is always column C. R1C1 notation also supports relative references, but a relative reference is interpreted from the cell containing the formula, so its result depends on where that formula is placed.

Common Errors and Troubleshooting

When INDIRECT returns an error or an unexpected value, check the text it is trying to interpret. A formula can be syntactically correct while the generated reference is invalid.

#REF! usually means the text does not describe a valid reference, or that a referenced worksheet or range does not exist. Check spelling, punctuation, and the sheet name.
#VALUE! can appear when ref_text is longer than Excel permits for a reference. Excel limits the text argument to 255 characters.
Wrong sheet or cell: inspect the assembled text for missing spaces, misplaced exclamation marks, or an incorrect row or column number.
Sheet names with spaces: enclose the sheet name in single quotation marks in the reference text, as in ‘Quarterly Data’!B3.
Blank or inconsistent labels: a blank selector or an unexpected label can build a reference that does not exist. Add validation or an error-handling approach appropriate to the workbook.

To diagnose a complicated formula, temporarily display the text being assembled in a spare cell. For example, if A2 contains a sheet name and the intended destination is B3, the text-building expression is ="'"&A2&"'!B3". With Quarterly Data in A2, it displays ‘Quarterly Data’!B3. Once the text is correct, use it as the ref_text argument to INDIRECT.

Performance, External Links, and Safer Alternatives

INDIRECT is a volatile function: Excel recalculates it whenever recalculation occurs, even if its apparent inputs have not changed. A handful of INDIRECT formulas is often manageable, but many such formulas across large workbooks can contribute to slower recalculation. Avoid using it by default when a direct reference or a nonvolatile function can do the job.

Another significant limitation is external workbooks. INDIRECT cannot resolve a reference to a workbook that is closed. A formula that uses text to point to an external file may return #REF! when that source workbook is not open. If a report must reliably retrieve data from closed files, consider a direct external link or a suitable import workflow rather than depending on INDIRECT.

For alternatives, match the tool to the task:

Use INDEX when you need to return a value or range based on row and column positions without constructing an address as text.
Use XLOOKUP for a value-based lookup in Microsoft 365 or Excel 2021 and later; it is usually clearer than building a cell address from text.
Use CHOOSE when a formula must select among a small, fixed set of alternatives.
Use direct sheet references when the source sheet is known and does not need to change dynamically.

These alternatives are not interchangeable in every situation. INDIRECT is specifically useful when the reference itself must be assembled from text, such as choosing among worksheet names stored in cells. For ordinary lookups, INDEX or XLOOKUP is generally easier to audit and does not carry INDIRECT’s volatility or closed-workbook limitation.

Frequently Asked Questions

Is INDIRECT volatile in Excel?
Yes. Excel treats INDIRECT as volatile, so it recalculates whenever Excel recalculates, regardless of whether its inputs appear to have changed. Large numbers of volatile formulas can affect workbook performance.

Can INDIRECT refer to another worksheet?
Yes, if the worksheet is in the same open workbook and the text forms a valid reference. Include the sheet name, an exclamation mark, and the cell or range address. Quoting sheet names is important when they contain spaces.

Can INDIRECT read a closed workbook?
No. INDIRECT cannot resolve an external reference when the source workbook is closed. Use another approach, such as a direct external link or an import process, if the source must remain closed.

What does the second argument in INDIRECT mean?
The optional a1 argument tells Excel how to interpret the reference text. TRUE or an omitted argument means A1 notation; FALSE means R1C1 notation, such as R2C3.

Why does INDIRECT return #REF!?
The generated text may not be a valid address, or the named sheet or range may not exist. Check the exact text, including sheet spelling, quotation marks, the exclamation mark, and the selected cell address.

Is INDIRECT available in Excel for the web and Mac?
INDIRECT is a longstanding Excel function and is available in current desktop Excel for Windows and Mac, as well as Excel for the web. Interface details can differ, but the function’s core syntax is the same.

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.