Can I use Excel's spilled range operator to extract a single 1D column from a 2D Dynamic Array?

Question

Summary

With Excel's new Dynamic/Spilled arrays, what is the best way to extract a single 1D spilled column from a 2D spilled array?

Details

Suppose I have a 2D Dynamic Array in Excel and it's spilling, so I don't know how many rows it has (and that may even change as whatever data is feeding into the array changes).

Now if I want to refer to the whole array, I can do so using #, the spilled range operator. For example, assume my array is in H3:L12 (although remember that while it currently has 10 rows, I cannot know that, nor can I know that it will not change). Now if I put into cell N8 (say), the formula =H3#, then I'll get a copy of my initial array in N8:R17.

But suppose all I want is a copy of the second column from the original array, I3:I12. Or suppose I want the original array, but after applying a filter. For example, perhaps I want a new array which has only the rows from the original where the second cell in each row is greater than 5. Is there a succinct way to do those kinds of of things, perhaps using the spilled range operator?

There are of course less-than-succinct ways. For example, although I've said that this being a Dynamic Array means I don't know the number of rows, in fact I can figure that out using ROWS(H3#). I could then use OFFSET() like this.

=OFFSET(H3,0,ROWS(H3#),1)

And then for the second case, where I want the full array but filtered such that the second column is greater than 5, I could have:

=FILTER(H3#,OFFSET(H3,0,ROWS(H3#),1)>5)

In neither formula am I explicitly mentioning a "hard-coded" number of rows, so the "Dynamic" nature is preserved, which is good. But it seems clumsy to have to go out of my way to extract that number of rows, and to then resort to OFFSET(), especially given that that is a volatile function. Isn't there a simpler, more built-in method for this?

thx.

Answer 1

To return the second column, use the Index function:

=INDEX(H3#,0,2)

And you can use the FILTER function to return a filtered array.

eg:

=FILTER(H3#,INDEX(H3#,0,2)>=5)

Answer 2

Consider the following User Defined Function:

Public Function Block2Column(rng As Range) As Variant
    Dim arr, OneCol(), cnt As Long, a

    arr = rng.Value
    cnt = rng.Count
    ReDim OneCol(1 To cnt, 1 To 1)
    i = 1

    For Each a In arr
        OneCol(i, 1) = a
        i = i + 1
    Next a
    Block2Column = OneCol
End Function

It can take multiple columns, or a block and create a single dynamic spill-down: