Excel Scan versus Reduce LAMBDA Issue

I don't know but I think they made the conscious decision not to support array of arrays in these functions because of all the potential ways things can go wrong. In your example and many examples there is a clear and correct output:

but if you try this:
=REDUCE(1,hstack( {2;3}, {4;5} ),LAMBDA(acc,cv,VSTACK(acc,cv)))
or in sheets you can go 'more directly' as
=REDUCE(1,{ {2;3}, {4;5} },LAMBDA(acc,cv,VSTACK(acc,cv)))
both result in
1
2
4
3
5
which at least is not the obvious output to me. and neither support SCAN in this case while I would have liked to see

LBROWN7 

Hard to say is that a bug or missed feature, depends on your attitude. Excel doesn't support array of arrays since its calc engine was designed so long before dynamic arrays are introduced. 

Different kind of workarounds exist, but no native support.

LBROWN7 

The behavior you have described is not a bug, but rather a difference in the implementation of array functions between Excel and Google Sheets.

In Excel, the REDUCE function is designed to work with arrays and perform operations like aggregation, whereas the SCAN function is not a built-in Excel function, so when you use it, Excel may not recognize it or its behavior may differ.

In Google Sheets, it appears that the SCAN function is implemented and designed to handle scanning through arrays with the provided lambda function.

So, when you use =REDUCE({1}, {2, 3, 4}, LAMBDA(acc, cv, VSTACK(acc, cv))) in Excel, it correctly uses the REDUCE function and works as expected.

However, when you use =SCAN({1}, {2, 3, 4}, LAMBDA(acc, cv, VSTACK(acc, cv))) in Excel, Excel doesn't recognize the SCAN function as a built-in function, and it appears to treat it as an array operation, leading to an error. In Google Sheets, the SCAN function seems to be recognized and works as expected.

So, it's not a bug but rather a difference in feature support between the two spreadsheet applications. If you need to use this specific behavior, you should use REDUCE in Excel and SCAN in Google Sheets, recognizing that they may not be interchangeable. The text was created with the help of AI.

My answers are voluntary and without guarantee!

Hope this will help you.

Was the answer useful? Mark as best response and like it!

This will help all forum participants.