Forum Discussion
Excel Scan versus Reduce LAMBDA Issue
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.
Hi NikolinoDE
I fully agree with you that there appears to be a difference in implementation between the SCAN and REDUCE.
That said, SCAN is intended to show the intermediate steps associated with a REDUCE function. I use SCAN to debug REDUCE functions.
SCAN and REDUCE should give the same end-result, given the same inputs.
How SCAN and REDUCE are implemented internally should not be a FACTOR.
My opinion after reading yours, SergeiBaklan, and mtarler responses is that it is a BUG, but of course, I could be wrong.