Forum Discussion

joelb95's avatar
joelb95
Copper Contributor
Apr 12, 2024

Weird Results from Array Manipulation - Any ideas on why?

Hi all - doing some exploring with lets and lambda in excel and passing arrays around.  I found a weird quirk where something that should work doesn't work as expected in a surprising way.  This prob...
excel
Formulas and Functions
SergeiBaklan's avatar
SergeiBaklan
MVP
Apr 13, 2024

joelb95 

Let start from first one

=LET(
    many, LAMBDA(choice,
        CHOOSE(
            choice + 1,
            10,
            "b",
            "harry",
            "c",
            SEQUENCE(4, 1, 5),
            SEQUENCE(3),
            45,
            "d",
            "lots",
            "e",
            "ten"
        )
    ),
    INDEX(
        LAMBDA(x, CHOOSE(x, (many(SEQUENCE(1, 2))))), 1, 1)(1)
)

INDEX returns the pointer on LAMBDA, (1,1) is the only possible combination. Since LAMBDA is first class function we may pass parameter to it after that. Thus that's equivalent of

LAMBDA(x, CHOOSE(x, (many(SEQUENCE(1, 2)))))(1)

If we would like to take something from returned by LAMBDA array, that could be like

=LET(
    many, LAMBDA(choice,
        CHOOSE(
            choice + 1,
            10,
            "b",
            "harry",
            "c",
            SEQUENCE(4, 1, 5),
            SEQUENCE(3),
            45,
            "d",
            "lots",
            "e",
            "ten"
        )
    ),
    INDEX( LAMBDA(x, CHOOSE(x, (many(SEQUENCE(1, 2)))))(1), 1, 1)
)

Resources