# INDIRECT function in Excel

> INDIRECT turns text into a reference: "B3" becomes the cell B3, and "A2:A"&D1 becomes a range whose last row comes from a cell.

Source: https://spreadsheetpractice.com/functions/indirect · Updated October 28, 2026 · Formulas and results on this page were run by the Spreadsheet Practice formula engine on October 30, 2026

## Quick answer

- **What it does:** INDIRECT turns text into a reference: "B3" becomes the cell B3, and "A2:A"&D1 becomes a range whose last row comes from a cell.
- **Syntax:** `=INDIRECT(ref_text, [a1])`
- **Most common use:** `=INDIRECT(D2)` returns 92 (The value in B3, the address written in D2)
- **Available in:** Every current version
- **Category:** [Lookup functions](https://spreadsheetpractice.com/functions/category/lookup)
- **Returns:** A reference to the cell or range named by the text; #REF! if the text isn't a valid address.
- **Google Sheets:** Works the same in Google Sheets, including the R1C1 form.

## What it does

INDIRECT turns text into a reference: "B3" becomes the cell B3, and "A2:A"&D1 becomes a range whose last row comes from a cell. Use it when the address itself is built by a formula. Because the address is text, it doesn't follow inserted rows, and it recalculates after every change, so prefer INDEX or XLOOKUP when they can do the job.

## Syntax

`=INDIRECT(ref_text, [a1])`

| Argument | Required? | What it is |
|---|---|---|
| `ref_text` | Required | The address as text, such as "B3", "A2:A10" or a cell that contains one. |
| `a1` | Optional | Optional: TRUE or omitted for A1-style text; FALSE for R1C1 style, such as "R3C2". |

## Examples

Each result was calculated by running the formula on the sample sheet.

| Cell | Formula | Result | What it shows |
|---|---|---|---|
| E2 | `=INDIRECT(D2)` | 92 | The value in B3, the address written in D2 |
| E3 | `=SUM(INDIRECT(D3))` | 255 | The text "B2:B4" used as a range |
| E4 | `=INDIRECT(D4,FALSE)` | 75 | R4C2 is row 4, column 2: B4 |

### The sample sheet

Every formula above runs on this data:

|  | A | B | C | D | E |
|---|---|---|---|---|---|
| 1 | Name | Score |  | Address | Result |
| 2 | Ana | 88 |  | B3 | 92 |
| 3 | Raj | 92 |  | B2:B4 | 255 |
| 4 | Lee | 75 |  | R4C2 | 75 |

## Common mistakes

| What you see | Why, and how to fix it |
|---|---|
| #REF! | The text isn't a valid address, has a typo, or names a sheet that doesn't exist. =INDIRECT("B3") needs the quotes; =INDIRECT(B3) reads the address written in B3. |
| The formula didn't follow inserted rows | INDIRECT("B3") is text, so inserting a row above doesn't turn it into B4 the way a normal reference would. |
| Slow recalculation | INDIRECT is volatile and recalculates after every change. Use INDEX with MATCH to pick ranges by position. |

## Tips

- To sum a column picked in a dropdown, INDEX is sturdier: =SUM(INDEX(B2:D10,0,MATCH(F1,B1:D1,0))).

## Related functions

- [OFFSET](https://spreadsheetpractice.com/functions/offset): OFFSET returns a reference that starts a given number of rows and columns away from a starting cell, optionally resized to a new height and width.
- [INDEX](https://spreadsheetpractice.com/functions/index): INDEX returns the value at a given row and column of a range: INDEX(A2:C10,4,2) is the fourth row, second column.
- [MATCH](https://spreadsheetpractice.com/functions/match): MATCH returns the position of a value in a row or column: 3 means the third cell.

## Questions

### What does INDIRECT do in Excel?

It converts text into a cell reference, so you can build addresses with formulas such as ="B"&ROW().

### How do I reference another sheet with INDIRECT?

Build the sheet name into the text: =INDIRECT("'"&A1&"'!B2"). The single quotes keep sheet names with spaces working.

### Does INDIRECT work with closed workbooks?

No. A reference to another workbook only works while that workbook is open; otherwise it returns #REF!.

## Practice

A graded INDIRECT exercise ("Read from an address") is on [the INDIRECT page](https://spreadsheetpractice.com/functions/indirect). It is checked instantly, then re-checked on changed data.

---

Spreadsheet Practice (https://spreadsheetpractice.com) teaches spreadsheet formulas by practice: every exercise is graded in a live grid and re-checked on changed data.