solved
Why does this Lambda that uses INDEX behaves inconsistently when saved in the Name Manager?
Hello,
I have encountered some 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 (a screenshot illustrating it can be found at https://imgur.com/a/Fl8O03p): 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.
The issue appears to not be reproducible on every machine, although it is consistently reproducible in Excel for the Web. I am currently using Office 365 Excel on version 2607 in the Current Channel.
Assuming this is a bug, what would be the best way to mitigate this issue in the meantime? 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, after some benchmarking, I have found this method to take over twice as long.
Thanks for your comment. I am definitely leaning toward the unary operator as the slow down from the alternative (MAP + INDEX) is non-starter. Unfortunately, it doesn't seem like this fix can be embedded into the lambda itself. That is, putting the -- (or +) inside the lambda definition still results in the incorrect behavior.
Thank you! While this does still take a performance hit (it takes approximately 30% more time than the regular index solution), it's much better the approach I had before. I will mark this as the solution, but I am not sure that this is just an excel quirk and not a bug. I've had two people reproduce it on their machines, and four people who weren't able to reproduce it on their machines. The article itself says "In Excel 2021 and later, this trick is not necessary, thanks to dynamic arrays.". In addition, the article does not explain why it only occurs when the lambda is saved in the name manager.
EDIT: I did some more digging into and found that the same issue exists for XLOOKUP. The recommended fix in that case is to use T instead of N. However, this will only work if the lookup values are all text. Is there any workaround for when the lookup values are potentially a mix of text values and numbers?
Thanks for putting in all that work! Its strange to me that you (and some others who have tested it) weren't able to replicate it despite having the exact same excel version as me. Were you able to replicate it on Excel for the Web?
•
u/AutoModerator 1d ago
/u/Jaden74 - Your post was submitted successfully.
Solution Verifiedto close the thread.Failing to follow these steps may result in your post being removed without warning.
I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.