INDIRECT function with inline array as parameter?

Regina Henschel 201 Reputation points
2024-09-25T13:51:21.5766667+00:00

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?

Office
Office
A suite of Microsoft productivity software that supports common business tasks, including word processing, email, presentations, and data management and analysis.
1,619 questions
Excel
Excel
A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data.
1,875 questions
0 comments

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Jiajing Hua-MFST 9,760 Reputation points Microsoft Vendor
    2024-09-26T02:42:09.5766667+00:00

    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.

    User's image

    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.

    User's image

    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"})).

    User's image

    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.

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.