Forum Discussion
Lambda that uses INDEX with array arguments behaves inconsistently when saved in the Name Manager
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))
);
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.
- Patrick2788Jul 29, 2026Silver 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!