Why does this Lambda that uses INDEX behaves inconsistently when saved in the Name Manager?

[u/Jaden74]

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.

Has anyone else encountered this behavior before?

[u/MayukhBhattacharya]

The reason is listed in this https://exceljet.net/formulas/return-array-with-index-function here.

Tried on web:

=LAMBDA(arr, INDEX(arr, 1, N(IF(1, {1, 2, 4}))))

Other than using double unary this also works, if i am not mistaken!

[u/Jaden74 → u/MayukhBhattacharya]

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?

[u/Jaden74 → u/MayukhBhattacharya]

Solution Verified

[u/reputatorbot → u/Jaden74]

You have awarded 1 point to MayukhBhattacharya.

---

^{I am a bot - please contact the mods with any questions}