Excel INDIRECT Function: Syntax and Examples

By Dr. Zubair Khalid, DVM, MS, PhD · 2026-10-03

The INDIRECT function in Excel converts a text string into a real cell or range reference. If a cell contains the text "B2", =INDIRECT("B2") returns whatever value sits in B2. The reference is resolved when the formula calculates, so you can build it from other cells, names or text formulas.

Quick Answer

INDIRECT takes a text string and returns the reference that string describes, such as "A1" or "Sheet2!B5".
Its syntax is INDIRECT(ref_text, [a1]). The second argument controls whether the reference is in A1 style (TRUE, the default) or R1C1 style (FALSE).
The text can come from a cell, a concatenation, or a text function, which is what makes the indirect formula in Excel useful for dynamic lookups.
INDIRECT is volatile. It recalculates on every change to the workbook, which can slow large models.
It cannot read a reference to a closed workbook, and it breaks if the referenced sheet name contains spaces and is not wrapped in apostrophes.

Syntax

Argument	Required?	Meaning
ref_text	Required	A text string that describes a reference. It can be a literal like `"A1"`, a cell that holds text, or a formula that builds text.
a1	Optional	TRUE or omitted uses A1 style (`B2`). FALSE uses R1C1 style (`R2C2`).

The function returns a reference, not a value, but Excel usually shows the value at that reference because it dereferences the result in most contexts.

How It Works

Excel normally reads a reference like B2 directly from the formula. INDIRECT does the opposite. It reads text and then interprets that text as a reference. This two-step process is the whole point of the function.

Consider a cell A1 that contains the text B2. The formula =INDIRECT(A1) looks at A1, sees the string B2, and returns the value in B2. If you change A1 to C2, the formula now returns the value in C2 without editing the formula itself.

The same logic applies to sheet names and ranges. =INDIRECT("Sheet2!A1") reads cell A1 on Sheet2. =INDIRECT("A1:A5") returns the range A1:A5, which you can feed into functions like SUM or COUNT.

The optional a1 argument matters when you build references in R1C1 notation. With a1 set to FALSE, =INDIRECT("R2C3", FALSE) points to row 2, column 3, which is cell C2. Most users leave this argument out and work in A1 style.

Because the reference is text, INDIRECT pairs well with text functions. You can join a column letter to a row number, or join a sheet name to a cell address, to build a reference that changes as your data changes. This is the core reason people search for the indirect function in Excel.

Worked Example

Suppose you have a small sales table. Cell A1 holds the text B2. Cell B2 holds the number 5. Cell B3 holds the number 3.

The formula =INDIRECT(A1) reads the text in A1, which is B2, and returns the value in B2. That value is 5.

Now change A1 to B3. The same formula returns the value in B3, which is 3. You did not touch the formula, only the text it points at.

You can extend this. If A1 holds B2 and A2 holds B3, then =INDIRECT(A1)+INDIRECT(A2) returns 5 + 3 = 8. Each INDIRECT call resolves its own text string to a cell, and the two values are added.

This is the pattern behind dynamic totals. Store the addresses you want in one column, then let INDIRECT read them.

More Examples

Build a reference from a row number. If A1 holds the number 2 and you want column B at that row, use =INDIRECT("B"&A1). When A1 is 2, this returns B2. When A1 is 5, it returns B5. The ampersand joins the column letter to the row number as text.

Point at another sheet. =INDIRECT("'"&A1&"'!B2") uses the sheet name stored in A1. The single quotes around the sheet name handle names that contain spaces. If A1 holds Q1 Sales, the formula reads cell B2 on the sheet named Q1 Sales.

Sum a dynamic range. =SUM(INDIRECT("A1:A"&B1)) sums column A from row 1 down to the row number in B1. If B1 is 10, the range is A1:A10. This lets a user type a row count and change the total.

Combine with a lookup. You can use INDIRECT to choose which column a lookup reads. If A1 holds a column letter, =INDEX(INDIRECT(A1&":"&A1), 3) returns the third item in that column. The Excel INDEX function handles the position lookup, and INDIRECT supplies the range.

Build text labels. INDIRECT also works with text functions when you need to assemble a reference from parts. The Excel TEXT function can format a number before you join it into a reference string.

Extract part of a reference. If your reference text is stored with extra characters, the Excel RIGHT function or the Excel FIND function can pull out the piece you need before INDIRECT reads it.

Errors and How to Fix Them

Error	Cause	Fix
#REF!	The text does not describe a valid reference, or the referenced sheet or cell was deleted.	Check the text string. Confirm the sheet name and cell address exist.
#REF! with a closed workbook	INDIRECT cannot read a reference to a workbook that is not open.	Open the source workbook, or replace INDIRECT with a direct link.
#REF! from a name	The text looks like a defined name that does not exist.	Confirm the name is spelled correctly and is defined in the workbook.
Wrong value	The text points at the wrong cell, often because of a stray space or a missing apostrophe.	Trim the text and wrap sheet names with spaces in single quotes.
#VALUE!	The a1 argument is not TRUE or FALSE.	Use TRUE, FALSE, 1 or 0 only.

Common Mistakes

Forgetting quotes around a literal reference. =INDIRECT(B2) reads the value in B2 and tries to use it as a reference. If you mean the address B2, write =INDIRECT("B2"). The fix is to add the quotes.
Leaving out apostrophes on sheet names with spaces. =INDIRECT("Q1 Sales!B2") fails. Write =INDIRECT("'Q1 Sales'!B2") so Excel treats the whole name as one sheet.
Expecting INDIRECT to read a closed workbook. It cannot. The fix is to open the source file or use a direct reference instead.
Using INDIRECT where a direct reference works. If the address never changes, a plain reference is faster and clearer. The fix is to reserve INDIRECT for cases where the reference must be built from text.
Ignoring the volatility cost. INDIRECT recalculates on every workbook change. In a large model with thousands of INDIRECT calls, the fix is to replace them with INDEX or a direct reference where possible.
Mixing A1 and R1C1 styles. If you build an R1C1 string but leave the a1 argument at its default, you get an error. The fix is to pass FALSE when you use R1C1 notation.

Limitations

INDIRECT cannot reference a workbook that is closed. If the source file is shut, the formula returns #REF! even when the path and cell are correct. This is a hard limit of the function, not a setting you can change.

The function is volatile, so it recalculates whenever anything in the workbook changes. In small sheets this is invisible. In large models it can slow recalculation noticeably. It also cannot create a reference from a value that Excel cannot parse as an address, so a typo in the text produces an error instead of a helpful message. Because the reference is built from text, INDIRECT formulas are harder to audit than direct references, and Excel's trace tools cannot follow the link to the source cell.

Frequently Asked Questions

What does the INDIRECT function do in Excel?

It turns a text string into a real cell or range reference. You give it text like "B2" or "Sheet2!A1", and it returns the value or range at that address. This lets a formula point at a location you build from other cells instead of typing the address directly.

What is the difference between INDIRECT and a normal reference?

A normal reference like =B2 is fixed in the formula. INDIRECT reads text and resolves it at calculation time, so the target can change when the text changes. That flexibility is the main reason to use it, and also the reason it is slower.

Why does my INDIRECT formula return #REF!?

The most common causes are a text string that is not a valid address, a sheet name that was deleted, or a reference to a closed workbook. Check the exact text the formula receives, confirm the sheet and cell exist, and open the source file if it is external.

Can INDIRECT reference another sheet?

Yes. Include the sheet name in the text, such as =INDIRECT("Sheet2!A1"). If the sheet name contains spaces, wrap it in single quotes, as in =INDIRECT("'Q1 Sales'!A1"). You can also build the sheet name from a cell.

Is INDIRECT slower than other lookup functions?

It is volatile, so it recalculates on every change to the workbook. That makes it slower than non-volatile functions like INDEX in large models. Use it when you truly need a reference built from text, and prefer direct references or INDEX when the address is fixed.

References

This article draws on the standard references listed under Further Reading.