Excel INDIRECT Function
last modified October 5, 2026
The INDIRECT function is a lookup and reference function in Excel
that turns a text string into a real cell reference. This tutorial provides a
comprehensive guide to using the INDIRECT function with detailed
examples. You'll learn the basic syntax, the difference between A1 and R1C1
styles, how to build references dynamically, and how to avoid the errors that
make this function so easy to get wrong.
INDIRECT Function Basics
The INDIRECT function converts a text string into a cell reference.
It allows you to create dynamic references that can change based on other cell
values. The function has two arguments.
| Component | Description |
|---|---|
| Function Name | INDIRECT |
| Syntax | =INDIRECT(ref_text, [a1]) |
| Arguments | ref_text: Required. Text reference a1: Optional. Reference style (TRUE for A1, FALSE for R1C1) |
| Return Value | Cell reference specified by text string |
This table breaks down the essential components of the INDIRECT
function. It shows the function name, syntax format, argument details, and
return value characteristics.
| Argument | Meaning |
|---|---|
| ref_text | Required. A text string that describes a reference. It can be an
A1-style reference such as "A1" or "A1:B5", an
R1C1-style reference such as "R1C1", a defined name such as
"Sales", or a reference to a cell that holds one of these as
text. If the text is not a valid reference, INDIRECT returns
#REF!. |
| a1 | Optional. A logical value that selects the reference style.
TRUE or omitted treats ref_text as an A1-style
reference. FALSE treats it as an R1C1-style reference. |
Because INDIRECT evaluates text at calculation time, Excel cannot
trace the dependency in the usual way. The function is also volatile, so it
recalculates on every change. Both points are covered later in this tutorial.
Basic INDIRECT Example
This example demonstrates the simplest use of INDIRECT to reference a cell specified by text.
| A | B |
|---|---|
| 100 | A1 |
| =INDIRECT(B1) |
The table shows cell A1 containing the value 100, and cell B1 containing the text "A1". The INDIRECT function in B2 converts the text "A1" into an actual reference to cell A1.
=INDIRECT(B1)
This formula takes the text "A1" from cell B1 and converts it to a reference to cell A1. The result will be 100, the value in A1. This shows the basic conversion of text to reference.
Building References Dynamically
Most real uses of INDIRECT join pieces of text with the
concatenation operator (&) to assemble a reference. The formula
below combines the letter "B" with the number in A1.
| A | B | C |
|---|---|---|
| 1 | 100 | =INDIRECT("B"&A1) |
| 2 | 200 | |
| 3 | 300 |
Because A1 contains 1, the formula builds the text "B1" and returns 100. Change A1 to 2 and the same formula returns the value in B2 (200), without any edit to the formula.
=INDIRECT("B"&A1)
INDIRECT with Sheet References
INDIRECT can point at another worksheet. When the sheet name has
no spaces, a simple concatenation is enough.
| A | B |
|---|---|
| Sheet2 | |
| =INDIRECT(A1&"!B5") |
Assume Sheet2 exists and its cell B5 contains 250. The formula in B2 combines the sheet name from A1 with the text "!B5" to form "Sheet2!B5" and returns 250.
=INDIRECT(A1&"!B5")
A sheet name that contains spaces or punctuation must be wrapped in single
quotes, exactly as it would be in a normal formula. If A1 holds "Sales Data",
the formula below builds the text 'Sales Data'!B5.
=INDIRECT("'"&A1&"'!B5")
Forgetting those single quotes is one of the most common causes of a
#REF! error with INDIRECT.
INDIRECT with Named Ranges and Data Validation
The ref_text argument can be a defined name as well as a cell
address. That makes INDIRECT the standard way to build dependent
dropdown lists, where the choices in one list depend on the choice in another.
Create two named ranges, Fruits (Apple, Banana, Orange) and
Vegetables (Carrot, Potato, Onion), on a list sheet. Put a category
dropdown in A1, then use the formula below as the Source of the data validation
applied to B1.
| A | B |
|---|---|
| Fruits | Apple |
| Vegetables | Carrot |
With A1 set to "Fruits", the validation list in B1 shows the members of the
Fruits range. Changing A1 to "Vegetables" makes B1 offer the
Vegetables range instead. The formula goes in the Data Validation
dialog box, not in a worksheet cell.
=INDIRECT(A1)
A1 vs R1C1 Reference Style
Excel can describe the same cell in two ways. In the familiar A1 style a
reference starts with a column letter followed by a row number, so
B2 means column B, row 2. In the R1C1 style the row comes first:
R2C1 means row 2, column 1, which is the same cell as
A2. The a1 argument of INDIRECT tells
Excel which style the text uses; it changes how the text is read, not the cell
that is finally referenced.
| Formula | Reference returned |
|---|---|
| =INDIRECT("A1") | A1 |
| =INDIRECT("B2") | B2 |
| =INDIRECT("R1C1", FALSE) | A1 |
| =INDIRECT("R2C3", FALSE) | C2 |
=INDIRECT("A1") ' A1 style, refers to A1
=INDIRECT("R1C1", FALSE) ' R1C1 style, refers to A1
=INDIRECT("R2C3", FALSE) ' R1C1 style, refers to C2
The first two formulas point at the same cell even though the text looks
different. Omitting the second argument has the same effect as passing
TRUE, so =INDIRECT("A1") and
=INDIRECT("A1", TRUE) are equivalent.
INDIRECT with a Dynamic R1C1 Reference
R1C1 text becomes useful when the row or column number is supplied by another
function. The example below uses ROW to build a reference to the
first column of the current row.
| A | B |
|---|---|
| 10 | |
| 20 | =INDIRECT("R"&ROW()&"C1", FALSE) |
| 30 |
The formula sits in B2, where ROW() returns 2, so the text becomes
"R2C1". That is the R1C1 name for cell A2, which holds 20. Copy the
formula down to B3 and it builds "R3C1", returning A3.
=INDIRECT("R"&ROW()&"C1", FALSE)
The FALSE argument is what makes Excel read the text as R1C1. The
same pattern works with COLUMN to build the column part of the
reference.
INDIRECT for Summing Variable Ranges
INDIRECT can return a whole range, not just a single cell. The
example below builds a range from a start address and an end address held in
separate cells.
| A | B | C |
|---|---|---|
| Start | A3 | |
| End | A5 | |
| 10 | ||
| 20 | ||
| 30 | ||
| =SUM(INDIRECT(B1&":"&B2)) |
B1 holds "A3" and B2 holds "A5", so the text becomes "A3:A5".
INDIRECT converts that text into a real range, and
SUM adds the values 10, 20, and 30 for a total of 60.
=SUM(INDIRECT(B1&":"&B2))
The same idea works with AVERAGE, COUNT,
MAX, and any other function that accepts a range argument.
INDIRECT with Table References
INDIRECT can resolve a structured reference to an Excel Table as
well. This lets a formula choose a table column from a cell value.
| A | B | C |
|---|---|---|
| Column | Price | |
| =SUM(INDIRECT("Table1["&B1&"]")) |
Assume a table named "Table1" has a Price column. Because B1 contains "Price", the formula builds the text "Table1[Price]", resolves it to the table column, and totals the values. If B1 held "Quantity", the same formula would total that column instead.
=SUM(INDIRECT("Table1["&B1&"]"))
The table must be in the same workbook, and its name and column headers must
match the text exactly. A typo produces #REF!.
INDIRECT Is a Volatile Function
INDIRECT is one of Excel's volatile functions. A volatile function
recalculates every time the workbook recalculates, even when none of the cells
it depends on have changed. The example below shows why that matters.
| A | B |
|---|---|
| Reference | A10 |
| =INDIRECT(B1) |
If you change B1 from "A10" to "A20", the formula immediately returns the value from A20. That part is expected. The performance cost is that the formula also recalculates when any unrelated cell in the workbook changes.
=INDIRECT(B1)
In a small sheet this is harmless. In a workbook with thousands of
INDIRECT formulas, each edit can trigger a full recalculation and
make the file feel slow. Where a non-volatile alternative can do the same job,
prefer it. INDEX is the usual replacement; OFFSET is
also volatile, so it does not help.
INDIRECT with External References
INDIRECT can build a reference to another workbook, but only while
that workbook is open. If the source workbook is closed, the formula returns
#REF!.
The standard external reference is written '[Book1.xlsx]Sheet1'!A1,
so the workbook name is enclosed in square brackets and the sheet name follows
it. The formula below assembles that text from three cells: A1 holds the file
name, B1 holds the sheet name, and C1 holds the cell address.
=INDIRECT("'["&A1&"]"&B1&"'!"&C1)
With A1 = "Book1.xlsx", B1 = "Sheet1", and C1 = "A1", the text becomes
'[Book1.xlsx]Sheet1'!A1. For closed workbooks, use Power Query, a
VBA procedure, or a direct external link instead. External references of this
kind are also not supported in Excel for the web.
INDIRECT vs. INDEX for Dynamic References
When the goal is simply to fetch a value by row and column number,
INDEX is usually the better choice because it is not volatile.
| A | B | C |
|---|---|---|
| Data | Row | Column |
| 10 | 2 | 1 |
| 20 | ||
| 30 | ||
| =INDEX(A2:A4, B2, C2) |
The range A2:A4 holds 10, 20, and 30. B2 supplies the row number 2 and C2
supplies the column number 1, so the formula returns 20. Unlike
INDIRECT, INDEX is non-volatile and recalculates only
when its inputs change.
=INDEX(A2:A4, B2, C2)
Use INDIRECT when the reference genuinely exists only as text, such
as a sheet name or defined name chosen at run time. Use INDEX when
you already have the numbers that locate the value.
INDIRECT Errors and Troubleshooting
| Cause | Symptom | Fix |
|---|---|---|
| ref_text is not a valid reference | #REF! error | Check the assembled text; select the text argument and press F9 to inspect it |
| Sheet name contains spaces but is not quoted | #REF! error | Wrap the name in single quotes: "'"&A1&"'!B5" |
| The referenced workbook is closed | #REF! error | Open the source workbook, or use Power Query or VBA |
| Named range or table name does not exist | #REF! error | Verify the name in Name Manager or on the Table Design tab |
| Reference is outside the grid limits | #REF! error | Stay within 1,048,576 rows and column XFD |
| External references in Excel for the web | #REF! error | External references are not supported there |
| Too many volatile formulas | Slow recalculation | Replace INDIRECT with INDEX where possible |
| Function name misspelled | #NAME? error | Spell the function as INDIRECT |
The INDIRECT function is a powerful tool for creating dynamic
references in Excel. It converts text into real references, which makes it
indispensable for sheet-qualified lookups, dependent dropdown lists, variable
ranges, and references that must survive inserted rows. It also has sharp edges:
it is volatile, it returns #REF! for invalid text, and it cannot
reach a closed workbook. Build the reference text carefully, quote sheet names
that contain spaces, and reach for INDEX whenever the reference can
be described with row and column numbers instead. Used with those rules in
mind, INDIRECT turns otherwise rigid formulas into flexible ones.
Author
List all Excel Formulas.