Why does this LAMBDA that uses INDEX with array arguments behave strangely when saved in the Name Manager?

Question

Why does this LAMBDA that uses INDEX with array arguments behave strangely when saved in the Name Manager?

Jaden Joshua Amatuzzo 20

Hello, I have encountered what appears to be 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. The formula "=LAMBDA(arr,INDEX(arr,1,{1,2,4}))({1,2,3,4})" correctly evaluates to {1,2,4}. Now, upon saving the lambda as TEST in the name manager, the formula "=TEST({1,2,3,4})" also seems to spill to the expected array {1,2,4}. However, when this result is passed to another function, the behavior becomes inconsistent. 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.

It appears that a named lambda using index with an array-valued row_num or column_num is not being treated as returning an array, despite spilling correctly when entered directly. I am unfortunately unable to attach a workbook to this post. In addition, it is not reproducible on every machine I have tested, but it is consistently reproducible in Excel for the Web. Here is a online workbook demonstrating this issue: [Moderator note: Personally Identifiable Information removed]. My current Excel is version 2607 on the Current Channel. Any insights into whether this is expected behavior or a bug would be appreciated.

Moved from Microsoft 365 and Office | Excel | Other | Windows

0 comments

Answer accepted by question author

Jade Ng 15,480 Microsoft External Staff Moderator

Dear @Jaden Joshua Amatuzzo,

Thank you for taking the time to provide such a detailed report, along with a clear minimal reproducible example. The comparison between the inline LAMBDA and the named LAMBDA behavior is especially helpful for troubleshooting.

Based on the behavior you described, it appears that when a LAMBDA containing an INDEX function with an array-valued row_num or column_num argument is saved in Name Manager, Excel may not consistently recognize the result as an array when it is passed to another function. Although the named LAMBDA correctly spills the expected values, functions such as SUM() and COLUMNS() may treat the result differently than they do when the same LAMBDA is evaluated inline.

Since the issue is reproducible in Excel for the Web and not consistently reproducible across all environments, this behavior does not appear to align with the expected results demonstrated by the inline LAMBDA examples you provided.

At this time, the behavior suggests a potential issue within the Excel calculation engine rather than a problem with the formula syntax itself. To assist with further investigation, I sincerely recommend you try to create a support ticket from: Microsoft 365 Admin Center > Support > Help & Support. You can raise support ticket from: https://admin.microsoft.com/#/support/requests

The support team over there has higher permission than us to investigate the issue from background, they have more resource and correct escalation channel in handling this kind of issues. This would be a more efficient way in handling the case for you.

For reference:  Get support | Microsoft Docs     

Thanks for your patience and understanding so far. Hope you having a nice day and really much warm regards to you as well!

Jaden Joshua Amatuzzo 20 Reputation points

2026-07-25T05:32:10.09+00:00

Hi, @Jade Ng thanks for getting back to me. Unfortunately, this is a school account so I do not have access to the Microsoft 365 Admin Center. Is there any other channel you would recommend I pursue this issue through?
Jade Ng 15,480 Reputation points Microsoft External Staff Moderator

2026-07-25T06:37:20.55+00:00

Dear @Jaden Joshua Amatuzzo,

Thank you for your response!

The steps I provided earlier for raising a support ticket apply to administrators who have access to the Microsoft 365 Admin Center. In your situation, I recommend contacting your school's IT administrator and asking them to create a Microsoft support ticket on your behalf so the issue can be investigated further.

Additionally, for broader community assistance, I recommend posting your question in the Microsoft Tech Community. There are many Excel experts and experienced community members who may have encountered a similar issue and can provide further guidance.

I understand how frustrating this situation can be, and I appreciate your time and cooperation. Thanks for your precious time. Have a nice day!

2 additional answers

Your answer

Jaden Joshua Amatuzzo 20 Reputation points

2026-07-25T05:32:10.09+00:00

Hi, @Jade Ng thanks for getting back to me. Unfortunately, this is a school account so I do not have access to the Microsoft 365 Admin Center. Is there any other channel you would recommend I pursue this issue through?
Jade Ng 15,480 Reputation points Microsoft External Staff Moderator

2026-07-25T06:37:20.55+00:00

Dear @Jaden Joshua Amatuzzo,

Thank you for your response!

The steps I provided earlier for raising a support ticket apply to administrators who have access to the Microsoft 365 Admin Center. In your situation, I recommend contacting your school's IT administrator and asking them to create a Microsoft support ticket on your behalf so the issue can be investigated further.

Additionally, for broader community assistance, I recommend posting your question in the Microsoft Tech Community. There are many Excel experts and experienced community members who may have encountered a similar issue and can provide further guidance.

I understand how frustrating this situation can be, and I appreciate your time and cooperation. Thanks for your precious time. Have a nice day!

Answer 1

Rory Archibald 19,055 Volunteer Moderator

There have historically sometimes been issues with forcing a return of an array of values from a function (see this page for example: https://excelxor.wordpress.com/2014/11/04/coercing-array-returns-from-cse-resistant-formulas/#more-2591

In this case, you can make your name work by just adding N() to your SUM formula:

=SUM(N(TEST({1,2,3,4})))

0 comments

Answer 2

Dana D 100

Hi. You have an "answer", but just to let you know, it works ok for me. (I would do it slightly differently thou)

User's image

Jaden Joshua Amatuzzo 20 Reputation points

2026-07-26T19:29:55.5966667+00:00

Interesting. Would you mind telling your Excel version and channel? Your version number can be found in File -> Account. Also, could you elaborate on what you mean by "would do it slightly differently thou"? Thanks.
Dana D 100 Reputation points

2026-07-27T20:59:14.3733333+00:00