Excel LAMBDA function, the basics

Content

Introduction

In March 2021, Microsoft announced the new Excel LAMBDA worksheet function. This new function enables us to define custom functions which may replace UDFs currently written in VBA. These custom functions are powerful for two reasons:

  1. Any user capable of writing a cell formula can now define new functions
  2. Because it uses built-in functions and the built-in multi-threaded calculation engine, these new LAMBDA functions perform significantly better than VBA UDFs.

This page explains the basics of this new LAMBDA function and how you turn it into your very own custom named function.

Please note that for this function to work you need Microsoft 365 and (at the moment of writing) you need to be on the so-called Office Insider program

Download the accompanying file

The LAMBDA function explained

There are some good articles out there on the new LAMBDA function already:

From the Microsoft Research blog: LAMBDA: The ultimate Excel worksheet function

From Microsoft Excel Help: LAMBDA function

If you are familiar with the LET function, the structure of the new LAMBDA function will look familiar to you. In its simplest form it might look like this:

=LAMBDA(x,x^2)

All arguments but the last are inputs to the function, the last argument of LAMBDA is the calculation returning the final result. This means a LAMBDA formula always has one more argument than the number of input values it needs. In the example above, x is the input and x^2 is the returned result.

Example: the Quadratic formula

Anyone who has taken math at school has heard of the equation of a parabola

y=ax²+bx+c

and likely also of the Quadratic formula:

The Quadratic Formula

The Quadratic formula solves the problem of determining the intersections of a parabola with the x-axis (the roots of the equation).

In Math terms: it solves x in ax²+bx+c= 0 for given a, b and c.

Translated to an Excel LAMBDA function, this looks like:

=LAMBDA(a,b,c,(-b+{-1,1}*SQRT(b^2-4*a*c))/2/a)

(the {-1,1} is Excel's way to get both results, it behaves as the +/- sign in the Quadratic Equation)

If you write this formula in a regular cell, Excel returns a rather unexpected result, a new Excel error code:

#CALC!

This is because we have not passed the required arguments to the formula. If placed in a cell like here, you must add the arguments -between brackets- behind the formula. Suppose our arguments are in cells A2, B2 and C2:

=LAMBDA(a,b,c,(-b+{-1,1}*SQRT(b^2-4*a*c))/2/a)(A2,B2,C2)

An example of the LAMBDA formula in a cell

Which results in:

An example of the LAMBDA formula in a cell

Transforming into a true function

By now you may be wondering what the fuss is about, this is still a (rather cumbersome looking) regular formula in a cell. Nothing fancy about it, now is it? But hold on, we'll get there. Open Excel's Name Manager (Formula tab, Name Manager button):

Excel's Name Manager

and click that "New..." button to add a new range name. Enter these details:

Adding a new LAMBDA name

Notice how I also took the extra effort of entering a comment, I'll show you why later on. After OK-ing, you can now enter a formula like this into a cell:

Using the LAMBDA name

Presto! A new user-created function!

As promised, here's why entering a comment for the name is useful. Select any empty cell and start typing =Qua, Excel shows the autocomplete drop-down which includes that comment:

The effect of the name comment

Now that is a great way to guide your users and tell them what the name is for!

Another example

Name of worksheet in cell

What makes the LAMBDA function useful is that it makes it easier to use complicated formulas. A good example is the formula to place the name of the current worksheet into a cell. Normally, one uses the CELL function for this:

=CELL("address",A1)

This gives you a result which looks like this:

C:\Users\piete\Documents\[Lambda Demo JKP.xlsx]Lambda Explanations

In this example, the name of the worksheet is "Lambda Explanations". To remove the preceding information, we must find the closing square bracket and take all text after that:

=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,LEN(CELL("filename",A1)))

Ugly and cumbersome. Let's use the LET function to make this easier:

=LET(f,CELL("filename",A1),MID(f,FIND("]",f)+1,LEN(f))

Now we're just two steps away from transforming this into a Lambda.

1. Add the LAMBDA function (the only argument is a cell on the worksheet from which you want to display the name)

=LAMBDA(cl,LET(f,CELL("filename",cl),MID(f,FIND("]",f)+1,LEN(f)))

2. Define a name called SheetName:

The SheetName name 

That's it, you can now get the sheet name into a cell with this simple formula:

=SheetName(A1)

Advanced formula environment

Microsoft has published a great tool to edit your Lambdas, called the Advanced Formula Environment. On your Insert tab, click "Get Add-ins":

Excel's Get Add-ins button

Then search for "Advanced formula" and click 'Add' next to this entry:

Advanced formula environment add-in

Sharing lambdas

Download the accompanying file

So what is the story around sharing lambda functions with your co-workers? Copying Lambda formulas from one workbook to another is relatively easy. If you want to copy a lambda function from workbook A to B, make sure you add the lambda formula to any cell in the original file. Next, select both the cell with the lambda function and all of its precedent cells (the cells which are the lambda arguments, those the lambda uses for the calculation) and copy them. Now go to any empty area in your other workbook and paste-special, formulas. After the paste, you can clear the pasted cells. If a lambda function internally uses other lambda functions, those get copied into the other workbook too.

You can also share your lambdas by creating a public gist in github:

And you can use other people's gists by importing them using the Advanced Formula Environment add-in.

The QuadraticEquation lambda is available by importing this gist link into that add-in:

https://gist.github.com/jkpieterse/568a6e267fb54ff71adb66299b447e70

My water97 lambdas are available in this gist: https://gist.github.com/jkpieterse/9f2f1d30f188ca20e3311e83b5a2a8a5

And I have a useful SheetName Lambda too:

https://gist.github.com/jkpieterse/1541a4c51afa490958563fd7a9fdca0b

Further reading

I've also written an article on how to convert VBA UDF functions to a LAMBDA function.

Fellow MVP Mourad Louha wrote an excellent article about using lambdas for array manipulation.


Comments

Have a question, comment or suggestion? Then please use this form.

If your question is not directly related to this web page, but rather a more general "How do I do this" Excel question, then I advise you to ask your question here: www.eileenslounge.com.




To post VBA code in your comment, use [VB] tags, like this: [VB]Code goes here[/VB].