Forum Discussion
SUBTOTAL, OFFSET, and bogus results
Doesn't =OFFSET(Values,0,0,SEQUENCE(10)) return an array of arrays that are different sizes? In my experience, excel won't return an array where the individual rows or columns are variable sizes to the worksheet. But, wrapping it with subtotal or index returns an array that is consistently sized, so that works.
I'm not yet familiar with the new lambda functions, so don't know if there is a way to try to resize the individual rows/columns so they're all consistent and fill in the empty elements?
Actually it returns array of references on the ranges. Result depends on what we do with that array. If land into the grid we resolve reference and actually that is, yes, array of arrays of different size.
However, we may take one element from such array and it returns it as an array
And, as we see, SUBTOTAL also works with such array. It aggregates each referenced range and returns an array of results.
