Is it possible to have array as an argument to IND

问题:

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.

回答1:

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.