Forum Discussion
Lambda that uses INDEX with array arguments behaves inconsistently when saved in the Name Manager
Hello,
I have encountered what appears to be inconsistent behavior when a particular type of lambda is saved in the Name Manager. The issue seems to occurs when the LAMBDA uses INDEX with either the row_num or column_num argument supplied as an array. The following is a minimal reproducible example: The formula "=LAMBDA(arr,INDEX(arr,1,{1,2,4}))({1,2,3,4})" correctly evaluates to {1,2,4}. Now, save the lambda as TEST (or any other name) in the name manager. The formula "=TEST({1,2,3,4})" also spills the expected array {1,2,4}. However, when the result is passed to another function, the behavior changes. For example, "=SUM(TEST({1,2,3,4}))" and "=COLUMNS(TEST({1,2,3,4}))" both evaluate to 1. In contrast, using the lambda inline instead of the defined name produces the expected results: "=SUM(LAMBDA(arr,INDEX(arr,1,{1,2,4}))({1,2,3,4}))" returns 7, and "=COLUMNS(LAMBDA(arr,INDEX(arr,1,{1,2,4}))({1,2,3,4}))" returns 3.
Unfortunately, I am unable to attach a workbook to this post. Additionally, the issue is not reproducible on every machine I have tested, although it is consistently reproducible in Excel for the Web. I am currently using Excel version 2607 on the Current Channel. Does this appear to be expected behavior, or is it a bug?
In the meantime, what would be the best way to mitigate this issue? I have found two potential workarounds. The first is to prepend the result of the named lambda with a unary + (e.g "=SUM(+TEST({1,2,3,4}))"), which appears to force excel to treat the result as an array. However, when using a shared lambda library (as is the case for most of my team), users generally do not know the implementation details of each lambda, so it is difficult to determine when this workaround is necessary. The second approach is to avoid passing an array to the row_num or column_num argument of Index by using MAP. For example, the TEST lambda defined above can be rewritten as =LAMBDA(arr,MAP({1,2,4},LAMBDA(idx,INDEX(arr,1,idx)))), which causes it to behave as expected. However, I am concerned about potential performance implications. Intuitively I would expect that a single INDEX call with an array argument would be more efficient than multiple INDEX calls wrapped in MAP, but I do not know whether this is the case.
Has anyone else encountered this behavior before?
7 Replies
- Patrick2788Silver Contributor
I updated an enterprise M365 today to version 2607 (Build 20228.20110) and the issue is still present. There's no sign of the problem on the Beta channel. The fix implemented there is apparently not ready for production yet. It'd be interesting to know more specifics.
- Patrick2788Silver Contributor
I do most of my development work in v2606 and I've noticed it. From what I've tested in Beta/Insider channel, the quirk does not exist.
The common thread in testing seems to be this quirk exists for lookup/retrieval functions (e.g., INDEX, VLOOKUP, XLOOKUP, etc.) but does not surface for shaping/selection functions (TOCOL, CHOOSEROWS, SORT, FILTER, etc.). BTW - I would not include Excel for the web in Lambda tests because that version doesn't fully support Lambda (e.g. Element-to-Apply (ETA) evaluation) and doesn't always resolve AFE modules well.
// These results require coercion TestAλ= LAMBDA( array, LET( arbit,INDEX(array,SEQUENCE(4),1), arbit )); TestBλ= LAMBDA( array, LET( arbit,VLOOKUP(TAKE(array,,1),array,2,0), arbit )); TestCλ= LAMBDA( array, LET( arbit,XLOOKUP(TAKE(array,,1),TAKE(array,,1),TAKE(array,,-1)), arbit )); // These results do not require coercion TestDλ= LAMBDA( array, FILTER(array,SEQUENCE(ROWS(array))) ); TestEλ= LAMBDA( array, CHOOSEROWS(array,SEQUENCE(ROWS(array))) ); TestFλ= LAMBDA( array, SORT(array) ); TestGλ= LAMBDA( array, TAKE(array,3) ); TestHλ= LAMBDA( array, TOCOL(array) ); TestIλ= LAMBDA( array, CHOOSECOLS(array,SEQUENCE(COLUMNS(array))) ); TestJλ= LAMBDA( array, WRAPROWS(TOCOL(array),COLUMNS(array)) );- JadenAmaCopper Contributor
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.
- Patrick2788Silver Contributor
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!
- m_tarlerSilver Contributor
not sure why this is happening but I was able to confirm and get this image of the desktop application on the left and the online version on the right of the exact same file showing the difference in results:
a couple other things of interest include the Excel Labs tool showing in both cases the correct answer and even identifying on the web version that it's answer doesn't match the result shown on the grid.
Also hovering on the indx function (which is the lambda function) on the web version is showing a #CALC! error. Although I would point to this as a place to start I will also note that if I highlight the indx function on the desktop it shows a result of 1 instead of the expected {1,2,4} so that isn't correct on the desktop either.
as a workaround I was able to break INDEX into a combination of CHOOSECOLS(CHOOSEROWS()) and that worked:
=LAMBDA(arr,CHOOSECOLS(CHOOSEROWS(arr,{1}),{1,2,4}))
Interesting the hover over on the web version still says #CALC! but now hover over on the app does correctly say {1,2,4}
- JadenAmaCopper Contributor
Thanks! It seems like it is a bug on Excel for the Web, but I am not sure as to why my desktop excel also has this behavior. Would you mind sharing your desktop excel version and channel?
- m_tarlerSilver Contributor
I'm on Version 2606 (Build 20131.20154) Current Channel
but noticing your version I just started the update and will let you know if that make a difference
and now on version 2607 (Build 20228.20110) and still works on desktop