INDIRECT function with inline array as parameter?

Question

Is it possible to use the INDIRECT function with an inline array as parameter?

={2\3\5} results in an array with 1 row and 3 columns and values 2 3 5.

Assume now you have value 2 in cell A1, value 3 in cell B2 and value 5 in cell C3. Then I expect that =INDIRECT({"A1"\"B2"\"C3"})results in the same array as ={2\3\5} . But it result in #VALUE! #VALUE! #VALUE!both as dynamic array and as CSE formula.

The simple form =INDIRECT("A2")results value 2 as expected.

Does it mean, that an inline array as parameter is not possible for the INDIRECT function?

Answer 1

Hi @Regina Henschel

If the parameter of the INDIRECT function is an array, and the elements of this array represent a cell range, not a single cell, a three-dimensional array will be returned after you press Ctrl + Shift + Enter, but the array data cannot be displayed entirely in the cells, just the first one data is shown.

Please note, it does not affect the continued calculation. So, if you add a sum function, such as =SUM(INDIRECT({"A1";"B2";"C3"})), it will return 10 correctly.

But if you just need to show all data of three-dimensional array, please add N formula, which is for numbers (T formula is for text in three-dimensional array) to convert to a general array, try this formula =N(INDIRECT({"A1";"B2";"C3"})).

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

---