INDIRECT in Google Sheets converts a text address into a real reference. For example, =INDIRECT("A2") returns the value in A2. It is useful when another cell controls the address, column, or tab to read.
Resolve a Cell Address Stored as Text
Enter this dataset starting in A1. Leave cells marked “(blank)” empty.
| Fruit | Text address |
|---|---|
| Apple | A2 |
| Banana | A3 |
| Cherry | A4 |
| Date | A5 |
Enter this formula in E2:
=INDIRECT(B2)The result is Apple. B2 contains the text A2; INDIRECT interprets it as an address. Filling the formula down returns Banana, Cherry, and Date from the addresses in B3:B5.
INDIRECT Function Syntax
=INDIRECT(cell_reference_as_string, [is_A1_notation])The first argument is address text, a named-range name, or a cell containing that text. The optional second argument defaults to TRUE for A1 notation. FALSE selects R1C1 notation.
Quotes distinguish a literal address from a reference. INDIRECT("A2") reads A2, while INDIRECT(B2) interprets whatever address text B2 contains. Invalid address text returns #REF!.
Build a Reference from a Row Number
Enter this dataset starting in A12. Leave cells marked “(blank)” empty.
| Row number | Product |
|---|---|
| 13 | Keyboard |
| 14 | Monitor |
| 15 | Mouse |
=INDIRECT("B"&A13)The result is Keyboard. The ampersand combines B with the number 13 to create B13. INDIRECT then reads that cell. A column letter and row number can both come from inputs.
Sum a Named Range Passed as Text
Enter this dataset starting in A22. Leave cells marked “(blank)” empty.
| Named range | Sales |
|---|---|
| GS30_Sales | 250 |
| (blank) | 300 |
| (blank) | 250 |
Select B23:B25 and define the named range GS30_Sales using Data → Named ranges. A23 contains that exact name as text. Then use:
=SUM(INDIRECT(A23))This returns 800: 250 + 300 + 250. The name must already exist. The name text alone does not create a range. A direct range string also works: =SUM(INDIRECT("B23:B25")).
Use R1C1 Notation
Enter 90 in C5, then use:
=INDIRECT("R5C3",FALSE)The result is 90. R5 means row 5 and C3 means column 3, equivalent to C5 in A1 notation. FALSE is required to interpret this text as R1C1.
Switch the Column a Formula Totals
Enter this dataset starting in A32. Leave cells marked “(blank)” empty.
| Month | North | South | West |
|---|---|---|---|
| Jan | 100 | 200 | 300 |
| Feb | 110 | 210 | 310 |
| Mar | 120 | 220 | 320 |
| Column | C |
=SUM(INDIRECT(B36&"33:"&B36&"35"))B36 contains C, so the constructed range is C33:C35. Its total is 630. Change the input to another valid column letter to point the formula at that column’s three monthly values.
Keep the selector outside the numeric data range. The quotes enclose the fixed row numbers and colon, while the two copies of B36 supply matching start and end columns.
Read Another Tab Using Its Name
Enter this dataset starting in C5. Leave cells marked “(blank)” empty.
| 90 |
|---|
=INDIRECT("'"&A43&"'!E2")This reads E2 from the GS30-SUM tab and returns 50 in the example workbook. Replace A43 with your tab name. The single quotes around the assembled name also support names containing spaces.
This pattern references tabs in the same spreadsheet. For another spreadsheet file, use IMPORTRANGE. An external file URL is not an A1 reference that INDIRECT can resolve.
Text addresses stay literal. An address written inside quotes does not adjust like a normal reference when rows or columns change. Recheck constructed addresses after changing the sheet layout.
Use direct references when the destination is fixed. They are easier to inspect and maintain. INDEX can also select a row or column without assembling an address string.
Other Google Sheets articles you may also like