Define a lambda function with infinite number of arguments

Question

Some Excel native functions like VSTACK permit of infinite number of arguments, and they have an intellisense as follows:

I would like to know how to define such a function by LAMBDA.

I tried try = LAMBDA(array1, [array2], [array3], [array4], 123) by optional arguments, the number of arguments is not infinite, and the intellisense is not exactly the same:

Would guess lambda doesnt support it or the [docs](https://support.microsoft.com/en-us/office/lambda-function-bd212d27-1cd1-4321-a34a-ccbf254b8b67) would mention it. Lambda is relatively new. — AndrewS, May 02 '22 at 02:51
I don't think you'll get that working since all parameters in `LAMBDA()` are pre-defined through your `TRY()` formula in the name manager. It's like you have build say `VLOOKUP()` with optional parameters that would still show through intellisense. `VSTACK()` does only take the exact same type of parameters and therefor are not pre-defined. Off-topic: what are you trying to achieve here? Or rather, what do you want to compute through this `TRY()` formula. — JvdV, May 02 '22 at 06:33

score 2 · Answer 1 · edited May 16 '22 at 06:04

Funny timing - I just worked on the same problem yesterday as I want to have access to the new functions, but I cannot do Early Adopters on my work PC. So I made a "home brew" version of each function. Here is VSTACK.

VSTACK =

LAMBDA( array1, array2, [array3], [array4], [array5], [array6], [array7], [array8], 

LET( 
    pattern, MAX(   2,
                    3*NOT(ISOMITTED(array3)),
                    4*NOT(ISOMITTED(array4)),
                    5*NOT(ISOMITTED(array5)),
                    6*NOT(ISOMITTED(array6)),
                    7*NOT(ISOMITTED(array7)),
                    8*NOT(ISOMITTED(array8)) ),
    stack, LAMBDA( array_1, array_2,
            LET( 
                rows1, ROWS( array_1 ), rows2, ROWS( array_2 ),
                columns1, COLUMNS( array_1 ), columns2, COLUMNS( array_2 ),
                rSeq, SEQUENCE( rows1 + rows2 ),
                cSeq, SEQUENCE(, MAX( columns1, columns2 ) ),
                IF( ISOMITTED(array_1),
                    array_2,
                    IF( ISOMITTED(array_2),
                        array_1,
                        IF( rSeq <= rows1,
                            INDEX( IF( array_1 = "", "", array_1), rSeq, cSeq ),
                            INDEX( IF( array_2 = "", "", array_2), rSeq-rows1, cSeq ) ) ) ) )
            ),
    SWITCH( pattern,
            2, stack(array1,array2),
            3, stack(stack(array1,array2),array3),
            4, stack(stack(stack(array1,array2),array3),array4),
            5, stack(stack(stack(stack(array1,array2),array3),array4),array5),
            6, stack(stack(stack(stack(stack(array1,array2),array3),array4),array5),array6),
            7, stack(stack(stack(stack(stack(stack(array1,array2),array3),array4),array5),array6),array7),
            8, stack(stack(stack(stack(stack(stack(array1,array2),array3),array4),array5,array6),array7),array8), )
    )
);

This is not an answer to your question. I know of no way to make a LAMBDA function with an infinite number of arguments, so I just made a crude but extensible way of adding more arguments without refactoring the whole thing.

NB: It is really crude, but this was a choice. I also thought of using recursion to avoid the nesting ad infinitum, but decided in the end that putting an iteration argument would be confusing and messy in comparison. My end objective was to produce "good enough".

Hello, thank you for the solution. I upvoted it; I leave the question open to show that the problem is not fixed (and hope one day Excel provides this capability). — SoftTimur, May 07 '22 at 16:08
Thanks @SoftTimur - me too. I like LAMBDA itself, the solution must be provided within Excel. I can imagine ways to notate an infinite set of arguments (e.g. as you can do in Python), but I am not sure how to refer to them after that. This would probably also require new Helper functions. In any case, we will have to create artificial sets and bloating our code until it exists. :-( — mark fitzpatrick, May 08 '22 at 06:30

Justin Lee · Answer 2 · 2022-07-27T03:18:40.257

Try this recursive version

STACK_V = LAMBDA([a_1], [a_2], [a_3], [a_4], [a_5], [a_6], [a_7], [a_8], [a_9],
    LET(
        a_cnt, 9-(ISOMITTED(a_1)+ISOMITTED(a_2)+ISOMITTED(a_3)+ISOMITTED(a_4)+ISOMITTED(a_5)+ISOMITTED(a_6)+ISOMITTED(a_7)+ISOMITTED(a_8)+ISOMITTED(a_9)),
        rows1, ROWS(a_1),
        rows2, ROWS(a_2),
        cols1, COLUMNS(a_1),
        cols2, COLUMNS(a_2),
        rowindex, SEQUENCE(rows1 + rows2),
        colindex, SEQUENCE(1, MAX(cols1, cols2)),
        stack, IFS(
                    ISOMITTED(a_1), a_2,
                    ISOMITTED(a_2), a_1,
                    rowindex <= rows1, INDEX(a_1, rowindex, colindex),
                    TRUE, INDEX(a_2, rowindex - rows1, colindex)
                ),
        IF(
            a_cnt < 2,
            a_1,
            STACK_V(stack, a_3, a_4, a_5, a_6, a_7, a_8, a_9)
        )
    )
);

Your answer could be improved with additional supporting information. Please [edit] to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers [in the help center](/help/how-to-answer). — Community, Jul 30 '22 at 09:55

Define a lambda function with infinite number of arguments

2 Answers2