Home Newsletter

Deze pagina in het NederlandsHome > Article index >

Excel LAMBDA function, the basics

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!

Sharing lambdas

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.

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)

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.

Please enter your name (required):

Your e-mail address (optional, will only be used to inform you when your comment is published or to respond to your question directly):

Your request or comment (max 2000 characters):

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

I give permission to process this data and display my name and my comment on this website according to our Privacy Policy.