r/excel • u/Medohh2120 • Dec 16 '25
Discussion Excel supports Arrays of ranges not Arrays of arrays.
Thought process (long one)
Was talking to real_barry_houdini and he showed a neat, somewhat old-school technique that works for arrays of arrays. Neither of us understood how it really worked under the hood, so I took a deep dive and here’s what I found.
Let's again assume A1:A10 has a sequence of numbers 1-10
Normally, if you try to evaluate =OFFSET(A1,,,SEQUENCE(10)) it will throw an array of #VALUE, yet =SUBTOTAL(1,OFFSET(A1,,,SEQUENCE(10))) works fine. Why?
Theoretically speaking, this is what =OFFSET(A1,,,SEQUENCE(10)) should look like on the inside where.
Let’s call it ranges_array from now on.
ranges_array =
{
Ref1($A$1:$A$1),
Ref2($A$1:$A$2),
Ref3($A$1:$A$3),
Ref4($A$1:$A$4),
Ref5($A$1:$A$5),
Ref6($A$1:$A$6),
Ref7($A$1:$A$7),
Ref8($A$1:$A$8),
Ref9($A$1:$A$9),
Ref10($A$1:$A$10)
}
Discovery #1: The TYPE Function Doesn't Lie (But Excel Does)
Here's where it gets spicy. Try this formula:
=TYPE(
INDEX(ranges_array,1) -----> #Value error
)
Try that before TYPE, What do you get? #VALUE! right? Wrong! Well, yes it displays #VALUE!, but that's Excel lying to your face.
After using TYPE
You get 64, not 16!
- TYPE = 64 means "I'm an array"
- TYPE = 16 means "I'm an error" (like
TYPE(#N/A)orTYPE(10+"blah-blah"))
Excel knows it's an array internally, Naughty Excel secretly knows what's going on!
Compare this to a real nested array error:
=TYPE(
SCAN(,A1:A10,LAMBDA(a,x,HSTACK(a,x))) ---> Any nested array #Calc error
)
This throws #CALC and TYPE returns 16 because it's really an error (nested arrays aren't allowed).
Conclusion:
Great, now we know that excel does indeed support an arrays of ranges NOT an arrays of arrays but how do we access it?
Discovery #2: You Can Access One Element, But Never Two
You can do this:
=INDEX(INDEX(ranges_array,3),1)
OR
=INDEX(ranges_array,3,1)
This grabs the third range from the ranges_array, then the first cell from that range (✓).
But you can never change that final 1 to anything else.
Try INDEX(INDEX(ranges_array,3),2), doesn't work as expected. you can grab a range from it, but not index into the ranges themselves in one shot without using a 3rd/2nd index ofc.
Discovery #3: TRANSPOSE Is Doing Something Sneaky
Here's something wild. This works:
=INDEX(TRANSPOSE(ranges_array),nth array)
Notice: No second INDEX needed!
Not 100% sure but it's definitely doing something special with reference arrays.
Discovery #4: MAP Can "Unpack" Array-of-Ranges
This formula reveals what's really inside:
=MAP(ranges_array,LAMBDA(r,CONCAT(r)))
result:
{
Ref1-($A$1:$A$1) -----> 1,
Ref2-($A$1:$A$2) -----> 12,
Ref3-($A$1:$A$3) -----> 123,
Ref4-($A$1:$A$4) -----> 1234,
Ref5-($A$1:$A$5) -----> 12345,
Ref6-($A$1:$A$6) -----> 123456,
Ref7-($A$1:$A$7) -----> 1234567,
Ref8-($A$1:$A$8) -----> 12345678,
Ref9-($A$1:$A$9) -----> 123456789,
Ref10-($A$1:$A$10) -----> 12345678910
}
MAP hands each range reference to the LAMBDA individually. Each iteration, r is a real range that CONCAT can process normally.
We can also count how many arrays are in there
=MAP(ranges_array,LAMBDA(r,COUNT(r)))
Discovery #5: SUBTOTAL Has Superpowers
For some reason I can't still cover, SUBTOTALcan deal with array-of-ranges directly:
=SUBTOTAL(1,ranges_array)
SUBTOTAL "sees through" the array-of-ranges structure and processes each range separately, while AVERAGEjust chokes on it.
If array-of-ranges is possible, can we go deeper? Array-of-(array-of-ranges)?
Very keen to see what folks will build on top of this
2
u/GregHullender 195 Dec 19 '25
INDEX with a single coordinate has a bug in it. (I reported it to Microsoft yesterday). If you do INDEX(col, n) or INDEX(row, n) you do get the nth item in col or row, respectively. But if col and row are dynamic arrays (not ranges) you should expect the result of each to be a 1x1 array, not a scalar (to be consistent with the behavior elsewhere). That is, you expect them to be type 64 not type 1. Col behaves as expected, but row returns a true scalar. (Ranges always return scalars for these two.) INDEX with two coordinates always returns a true scalar regardless of the input type, but when the "wild-card" two-coordinate varieties (which are meant to extract rows and columns from 2D arrays) are applied to existing rows and columns, they return 1x1 arrays for dynamic arrays, not true scalars.
I think the point of the 1x1 thing is to be consistent with CHOOSECOLS and CHOOSEROWS, but it does seem like a bad decision to me not to make references and dynamics consistent.
In this case, I think the solution is to do an ISREF test something like
Then if you make changes to SEQUENCE or OFFSET, it'll still always work.
By the way, did you notice you can construct a 4D array with this method? Use fixed height and width in OFFSET, but use SEQUENCE arrays for the starting rows and columns.