EXCEL: Array in a cell. How to get the array back?

Question

Let's say I have in B2 the formula =ROW(A1:A3). This formula gives an array of {1;2;3}. Because a cell only holds one value, cell B2 displays a value of 1. Any place in the worksheet, the formula =B2 gives 1, not the array. Yet, Excel still remembers the array because the formula is still in B2.

Is there any way to get the array back so it, the whole array, not its individual elements, can be used for further manipulation? I'm looking for something like OPERATION(B2) = {1;2;3}. For example, SUMPRODUCT(OPERATION(B2)) = SUMPRODUCT(ROW(A1:A3)) = 6.

Answer 1

As a workaround, you can store your formula in Name Manager, e.g.:

Then you can use it as a reference in Excel formulas, like =INDEX(Rows,2,1):

Answer 2

I realize that this is not the answer to the OP's question as they do not have the latest Office 365. I put this here for those who come later and do have it.

---

With the latest Office 365 with Dynamic array formulas this problem is now gone.

Putting =ROW(1:3) or the equivalent dynamic array formula =SEQUENCE(3) Excel will spill the array down automatically without the need of Ctrl-Shift-Enter:

And now one can refer to the array by only refering to A1 by putting # after the reference:

=SUM(A1#)

Then no matter how the array in A1 changes the other formula does not need to be changed.

The caveat is that the formula in A1 needs the room to spill down. If there is a cell that has anything in it that resides in the spill down array it will return an error.