I don't know if the rest of you love lambdas as much as I do but I have been using them extensively for the past few months and would like to share one of the tricks I have learned.

First lets start off with covering the simplest way of implementing recursive lambdas (which appears to be the method that Microsoft wants us to always use), the name manager... if we wanted to define a simple factorial function F() we would create a new name 'F' and put the following in it =LAMBDA(X,IF(X>1,X*F(X-1),1)) as you can see recursion works the way we would expect it to in 99% of programming languages; the function simply calls itself by name inside of its definition. For every sane person this is enough and they will define all of their recursive lambdas this way... but I had to see how far I could push it...

My dilemma was that I had a complicated operation that required helper functions, but I didn't want my namespace cluttered up with these helper functions and I didn't want users to be able to call them as they should only be called inside of my lambda function. My first instinct was to use the LET() function and it worked exactly as I wanted... but only for non recursive helper functions; for example if I wanted to define a function that rotated a matrix by 180 degrees I could do something like this:

=LAMBDA(Mat,
  LET(EM, LAMBDA(n, MAKEARRAY(n, n, LAMBDA(i, j, --(j=(n-i+1)))),
    MMULT(MMULT(EM(COLUMNS(Mat)),Mat),EM(ROWS(Mat)))
  )
)

The Lambda takes in a matrix 'Mat' and then multiplies it by 2 exchange matrices, we define the lambda EM to create an exchange matrix of arbitrary size n, then call it twice in our final Lambda definition which simply performs 2 matrix multiplications.

But what are we to do if we want to define a recursive lambda outside of the name manager?
As an example lets simply try defining and applying a recursive lambda inside of a let function... we will use the factorial definition from above:

=LET(
  F,LAMBDA(X,IF(X>1,X*F(X-1),1)),
  F(5)
)

When trying the formula above I always got a #NAME? Error because when evaluating the definition of 'F' the value of 'F' is not yet defined. This is similar to the problem encountered by C compilers when you call a function before it is defined. In C you can solve this issue with function prototypes (usually in a header file) which act as a 'temporary definition' of a function telling the compiler the input(s) & their type(s) and return type until the real definition happens later on in the code, but because the evaluation strategy of LET() is strict the definition of any name is immutable so we need to define it fully the first time.

For the past few months I thought it was impossible to work around this limitation but I finally figured it out, it turns out that all the time I spent learning lambda calculus was not pointless.

In lambda calculus a lambda expression can not refer to itself by name in its own definition, this is similar to the constraints of defining names in the Let() function. We can get around this limitation through the use of a fixed point combinator, the most famous of which is Curry's Y Combinator [Y := λg. (λx. g (x x)) (λx. g (x x))] but to solve this problem we need to use the Turing fixed-point combinator (also known as the theta combinator) [Θ := (λxg. g (x x g)) (λxg. g (x x g))] translated from lambda calculus to Excel formulas it works like this:

=LET(
  F,LAMBDA(G,X,IF(X>1,X*G(G,X-1),1)),
  F(F,5)
)

By applying the logic of the combinator to our lambda function we are no longer calling the function F inside of the definition of F, but instead calling the function G, which is passed to F as a parameter. when we finally want to call our 'recursive function F, we also pass the function to itself as its first parameter.
If you want to make your final formula more readable you can also curry the lambda to make it callable without having to pass itself as a parameter, but this does incur some minor extra overhead because you have to wrap it up in another lambda expression:

=LET(
  Y,LAMBDA(G,X,IF(X>1,X*G(G,X-1),1)),
  F,LAMBDA(X,Y(Y,X)),
  F(5)
)

--