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).
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.
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:
It is a bit hackish, but it certainly works and doesn't need any VBA nor addons. And it is fast.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With