Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Is it possible to have array as an argument to INDIRECT(), so INDIRECT() returns array? [closed]

Suppose, that I have a vector of cells A1:A5, each with string that is an address of the other cell.

What I want to do, is to have a function, that returns an array of values from these referenced cells. See the example - I want the formula to return 15 (it returns 1).

enter image description here

In production setting, this vector is much longer (ca. 2000 rows).

What would solve the problem, is array-capable INDIRECT().

(I realize, that I might be pushing Excel beyond its the limits)...


My further thoughts on this topic:

Because the INDIRECT() is capable of producing arrays even from a single-cell input, such theoretical "INDIRECTARR()" formula would either require nested (i.e. multidimensional) array computation support in Excel, or will be intrinsically limited to arguments that denote one-dimensional ranges.

like image 914
Adam Ryczkowski Avatar asked Nov 05 '13 10:11

Adam Ryczkowski


1 Answers

Finally I've found a way to solve it. As long as the values are on one sheet, we can take advantage of the fact, that when the second and/or the third argument to INDEX is an array, the result is an array as well:

enter image description here

It is a bit hackish, but it certainly works and doesn't need any VBA nor addons. And it is fast.

like image 56
Adam Ryczkowski Avatar answered Oct 02 '22 02:10

Adam Ryczkowski