Description
Returns the reference specified by a text string.
NOTE
- INDIRECT is a volatile function.
- This function is often used when the reference range needs to be changed.
- For example, with =SUM(A1) you won’t be able to change the reference, but =SUM(INDIRECT(A1)) can return =SUM(A1), =SUM(A2), =SUM(A1:A2),.. depending on the content of A1.
Syntax
Copied!=INDIRECT(ref_text, [a1])
SAMPLE data
| | A | B | C | D | E |
|:---:|:----|:-----------|:----|:----|:----|
| 1 | | | | | |
|-----|-----|------------|-----|-----|-----|
| 2 | | | | | |
|-----|-----|------------|-----|-----|-----|
| 3 | | 4/4/2010 | | | |
|-----|-----|------------|-----|-----|-----|
| 4 | | 09/08/2012 | | | |
|-----|-----|------------|-----|-----|-----|
| 5 | | 15/11/2024 | | | |
|-----|-----|------------|-----|-----|-----|
| 6 | | | | | |
|-----|-----|------------|-----|-----|-----|
| 7 | | | | | |
|-----|-----|------------|-----|-----|-----|EXAMPLE 1
Copied!//𝘨𝘦𝘵 𝘵𝘩𝘦 𝘤𝘰𝘯𝘵𝘦𝘯𝘵𝘴 𝘰𝘧 𝘵𝘩𝘦 𝘳𝘦𝘧𝘦𝘳𝘦𝘯𝘤𝘦: =INDIRECT("B3:B5")
| | A |
|:---:|------:|
| 1 | 40272 |
|-----|-------|
| 2 | 41130 |
|-----|-------|
| 3 | 45611 |EXAMPLE 2
Copied!//𝘪𝘯 𝘵𝘩𝘦𝘰𝘳𝘺 𝘵𝘩𝘪𝘴 𝘧𝘶𝘯𝘤𝘵𝘪𝘰𝘯 𝘸𝘪𝘭𝘭 𝘳𝘦𝘵𝘶𝘳𝘯 𝘢𝘯 𝘢𝘳𝘳𝘢𝘺 𝘰𝘧 𝘳𝘢𝘯𝘥𝘰𝘮 𝘴𝘪𝘻𝘦: =ROW(INDIRECT("A1:A" & RANDARRAY(1, 1, 1, 10, 1)))