Forum Discussion
Lambda that uses INDEX with array arguments behaves inconsistently when saved in the Name Manager
Thanks for the comment! Unfortunately, I do not have access to the beta/insider channel as I get office 365 through my university, but hopefully they merge whatever fixes the issue soon. I did some more digging since I have found this interesting article: https://exceljet.net/formulas/return-array-with-index-function. Apparently, before dynamic arrays were introduced, this was known behavior of INDEX and VLOOKUP. However, with the introduction of dynamic arrays, this issue was fixed (as is the case when you evaluate the lambda inline). It seems like the name manager impedes the new behavior and falls back to the old, incorrect, behavior. Why this impacts only some machines is still a mystery to me.
There's a good bit about dynamic arrays, LET, and LAMBDA that's undocumented officially. Another quirk I've seen is what happens when TOCOL is used on a scalar and then handed to another function. At the sheet level, one would never need to flatten a scalar, but within the constructs of a function, there may be a need to flatten a return that's expected to be 2D (maybe with some edge cases where scalars are returned).
Another part of the mystery is how Excel parses Lambda in the AFE workbook module. In certain cases, the function breaks because "return" is assigned by LET in a certain context. It's an old Excel 4.0 macro reserved word, if I'm not mistaken. I've learned to avoid using "return".
There's always an adventure!