Excel LAMBDA Function

Since Excel released the LAMBDA function, it has been the talk of the town.

Lambda is different from other Excel functions. While all the other Excel functions do some calculations and give you the result, Lambda allows you to create your own functions.

That’s right – you don’t need to resort to VBA or any other programming language to create your own functions. You can do that right within a cell using LAMBDA.

To put it in simple words, the Lambda function allows you to create your own custom functions in Excel, which helps you simplify and use complex formulas.

In this article, I will start with the basics of the Excel LAMBDA function and show you how it works.

While the first few examples may seem very basic (and may even make you feel that the Lambda function is worthless), as I progress through these examples, you will notice that the LAMBDA function can actually be quite powerful when it comes to simplifying your formulas.

Finally, the recursive example shows how a LAMBDA can call itself to apply a series of replacements.

Lambda function can recursively call itself and even call other Lambda functions you have created. Examples of how to use recursive lambdas are also covered in this article.

Enough of the talk.

LAMBDA lets you name inputs and use them in a calculation.

Excel LAMBDA Function Syntax

Before I get into the fun of creating Lambda functions, let me quickly tell you the syntax:

=LAMBDA([parameter1, parameter2, …,] calculation)

Here:

• [parameter1, parameter2, …,] – These are the values that you want to use in the formula that you will create using LAMBDA. These could be cell references, numeric values, or text values. You can use up to 253 parameters in one single lambda formula. Also, this is an optional argument.
• calculation – This is the formula that you want the Lambda function to use to calculate and give you the result. This is the required argument and should return a value.

Now, let me first show you how to create a very basic Lambda in Excel.

Creating a Basic Lambda Function

Below, I have two numbers that I want to add.

Now, if you have to add these two numbers, you can use either of the two following formulas:

=A2 + B2

or

=SUM(A2,B2)

Now, let me show you how to create a Lambda function that takes two arguments (the cell references) and gives you the sum of these two numbers.

At this point, I am fully aware that this is a useless Lambda, and I can easily use any of the above two formulas instead. But my aim here is not to show you a practical example of using the Lambda function but rather use a very simple use case to explain how the Lambda function works in Excel. More practical examples are covered later in this article.

So, let’s create that Lambda.

Enter the following formula in the cell:

=LAMBDA(a,b,a+b)(A2,B2)

You’ll notice that it gives us the right result – 30.

Now, let me explain what’s going on.

=LAMBDA(a,b,a+b) – This part of the formula is the Lambda function we have created. It takes two arguments (a and b), and the last argument is the calculation, which tells the formula what to do with these arguments.

So, in our case, the calculation is to do a + b.

Now, if you just enter =LAMBDA(a,b,a+b) in a cell and hit enter, you will see the calculation error (#CALC!).

This formula defines a LAMBDA but doesn’t call it, so Excel returns #CALC!. Appending (A2,B2) calls it with values for a and b.

So, we give these values just like we would give them in a SUM function, within brackets.

So, our final formula becomes

=LAMBDA(a,b,a+b)(A2,B2)  

So, think of it like this – we have created a lambda function that adds two values, and then we have given Lambda the two values that we want to add.

Now, while this works, this is not how Lambda is supposed to be used.

Excel allows you to put the LAMBDA function in a named range and then use the name of that range as a formula.

For example, let me save this Lambda formula with the name AddTwoNos and then I will be able to use the function AddTwoNos in the worksheet just like any other regular function.

Here are the steps to save a Lambda function as a named range:

1. Copy the Lambda function =LAMBDA(a,b,a+b). Note that I’m not copying the arguments I passed to the function in the brackets. Just the Lambda formula
2. Hold the Control key and then press the F3 key to open the Name Manager dialog box. Alternatively, you can also click on the Formulas tab and then click on Name Manager.

2. Click on the New button. This will open the new name dialog box

3. Enter a name for the function. In this case, I will use AddTwoNos

4. In the ‘Refers to’ field, copy and paste the Lambda formula

5. Click OK

With Workbook scope selected, we can use AddTwoNos in any worksheet in this workbook.

Below is the formula that uses our Lambda formula calculation to add two numbers.

=AddTwoNos(A2,B2)

This will give us the result as 30.

Note: When you create a custom function with LAMBDA and then use it in a cell in Excel, you will see the function show up in the intellisense as you type the first few characters of the function name.

If you are with me so far, here are two things to know:

• You’re awesome!!
• We’ve seen how to create a very basic lambda that enables us to create our own custom function, specify what arguments it should take and what calculation should happen in the back end, give a name to that function, and then use it in the worksheet just like any other regular function.

At this point, if you don’t share my enthusiasm, I don’t blame you.

But let me tell you this: what we have done forms the basis of some amazing things we can do with the Lambda function in Excel.

Examples of Using the LAMBDA Function in Excel

Let me now take you through some practical examples where you can think of using lambda in your work.

Calculate Commission Using LAMBDA Function (One Condition)

Let’s start with an easier one.

Below, I have a data set where I have names, regions, and sales values for some employees.

In this data set, I want to calculate commissions in column D based on the following criteria:

• Commission is 5% if the Sales value is more than 50,000
• Commission is 3% if the Sales value is 50,000 or less

I can do this using a simple if statement as shown below:

=IF(C2>50000,C2*5%,C2*3%)

Now, let me show you how to create a Lambda function from this.

Again, I am aware that I can use the IF function instead of creating the Lambda function. But the idea here is to showcase how the Lambda function can be created and used, and then I’ll show you more complex examples where creating a Lambda would actually be helpful.

Below is the Lambda function that I’ve created:

=LAMBDA(sales,IF(sales>50000,sales*5%,sales*3%))

In this function, I have provided one parameter (sales), and then the if function calculation that uses the sales parameter to calculate the commission.

If I enter this formula in a cell and hit the Enter key, it will give me the calculation error (#CALC!)

This formula defines a LAMBDA without calling it. Appending (C2) calls the function and passes the sales value from C2.

I can get it to work by using the below formula instead:

=LAMBDA(sales,IF(sales>50000,sales*5%,sales*3%))(C2)

Here, I have added (C2) after the LAMBDA function, so it knows that it can take the value in cell C2 as the value of the sales parameter.

Now that I know that my Lambda function works, let me create a custom function with this so that I can use a simplified formula to calculate commission instead of the IF function.

Here are the steps to do this:

1. Copy the Lambda function =LAMBDA(sales,IF(sales>50000,sales*5%,sales*3%)). Note that I’m not copying the arguments I passed to the function in the brackets. Just the Lambda formula
2. Hold the Control key and then press the F3 key to open the Name Manager dialog box.
3. Click on the New button. This will open the New Name dialog box
4. Enter a name for the function. In this case, I will use SalesCom

5. In the ‘Refers to’ field, copy and paste the Lambda formula

6. Click OK

The above steps have converted my Lambda function into a Named Range, and now I can use SalesCom as any other regular function in my worksheet.

So, I can now use the below function to get the commission:

=SalesCom(C2) 

As you can see, this is simpler than using the longer IF function.

Also, in this case, I have hard coded the commission percentage values in the formula, but if you want, you can have those values in a cell reference and then use that cell reference instead.

Now, if you’re still thinking that the LAMBDA function hasn’t proved itself useful yet, as the IF function was already simple enough, let me show you a slightly complicated function that can be further simplified using the Lambda function in the next example.

Calculate Commission Using LAMBDA Function (Two Conditions)

Below, I have the same data set with names, regions, sales, and commission columns, and I want to calculate the commission based on the following conditions:

• If the region is the US, the commission would always be 5%, irrespective of the Sales value.
• If the region is not the US, then the commission would be 5% if the sales value is more than 50,000
• Else, the commission would be 3%

Below is the nested IF function that can do this:

=IF(B2="US",C2*5%,IF(C2>50000,C2*5%,C2*3%))

Now, let’s convert this function into a Lambda function, so that I can create my own custom function, which is a lot more simple than the above nested IF function.

In the above function, since there are two different cell references I’m using (B2 and C2), I will have to create a Lambda function that uses two parameters before the calculation.

Here is the LAMBDA function that works:

=LAMBDA(region,sales,IF(region="US",sales*5%,IF(sales>50000,sales*5%,sales*3%)))

If you enter this formula in a cell and press Enter, Excel returns #CALC! because the LAMBDA has not been called. Appending (B2,C2) calls it with values for region and sales.

I can test whether my Lambda function is working or not by using the below formula in a cell in Excel.

=LAMBDA(region,sales,IF(region="US",sales*5%,IF(sales>50000,sales*5%,sales*3%)))(B2,C2)

Since the Lambda function is working fine, let me use this to create my own function.

Here are the steps to convert the Lambda function into a named range so that I can use that named range as a custom function in my worksheet:

1. Copy the Lambda function =LAMBDA(region,sales,IF(region=”US”,sales*5%,IF(sales>50000,sales*5%,sales*3%))). Note that I’m not copying the arguments I passed to the function in the brackets. Just the Lambda formula.
2. Hold the Control key and then press the F3 key to open the Name Manager dialog box.
3. Click on the New button. This will open the New Name dialog box
4. Enter a name for the function. In this case, I will use SalesCom2

5. In the ‘Refers to’ field, copy and paste the Lambda formula

6. Click OK

Now that I have saved my Lambda function as a named range, I can use that as a custom function on my worksheet.

Below is the formula I can use:

=SalesCom2(B2,C2)

As you can see, I am able to replace a nested If function with a much simpler Lambda function that takes just two arguments.

As you get comfortable in creating Lambda function in Excel, you will be able to convert long and complicated formulas into your own simpler custom functions.

By now, I hope you have a decent understanding of how the lambda function can be created and used in Excel.

Now, let me show you a couple more examples of creating Lambda functions in Excel.

Also read: Creating a User Defined Function (UDF) in Excel VBA

Extract Numbers from a String using the LAMBDA Function

Below, I have a data set with product IDs in column A, and I want to extract their digits as text in column B.

I can do that using the Excel formula below:

=TEXTJOIN("",TRUE,IFERROR(MID(A2,SEQUENCE(LEN(A2)),1)*1,""))

The above formula separates each character in the cell and analyzes whether it’s a number or not. If it is a number, it is kept, and if it is not a number, it is replaced by a blank.

As you can see, this is not a very simple formula, and if I convert this into a Lambda function, I would be able to use a very simplified function that takes one single argument and gives me the numeric part from the cell.

So, let’s create the LAMBDA function for this.

Below is the lambda function where I’ve used the parameter pdid (for product ID):

=LAMBDA(pdid,TEXTJOIN("",TRUE,IFERROR(MID(pdid,SEQUENCE(LEN(pdid)),1)*1,"")))

If you enter this formula in a cell and press Enter, Excel returns #CALC! because the LAMBDA has not been called. Appending (A2) calls it with the product ID from A2.

So, if you want to check the formula, you can use the below formula instead, where I give the value of pdid in brackets after the formula.

=LAMBDA(pdid,TEXTJOIN("",TRUE,IFERROR(MID(pdid,SEQUENCE(LEN(pdid)),1)*1,"")))(A2)

The above formula returns the text “231”. TEXTJOIN keeps the result as text, so leading zeros are retained too. For example, a product ID of A-0012 returns “0012”.

Now, let me create my own simplified version of this formula. I will call that formula GetNum.

Below are the steps to create my own custom function using the above lambda function:

1. Copy the Lambda function =LAMBDA(pdid,TEXTJOIN(“”,TRUE,IFERROR(MID(pdid,SEQUENCE(LEN(pdid)),1)*1,””)))
2. Open the Name Manager dialog box (you can do this by using the keyboard shortcut Control + F3).
3. Click on the New button. This will open the New Name dialog box
4. Enter a name for the function. In this case, I will use GetNum

5. In the ‘Refers to’ field, copy and paste the Lambda formula we copied in step 1

6. Click OK

Now, I can use the below function to get only the numeric part from a cell:

=GetNum(A2)

As you can see, I was able to convert a relatively complex formula into an extremely simple one that takes one single argument and gives me the expected result.

It can be quite a time saver if you need to use this formula in multiple places in your workbook. So you can create it once and reuse it as many times as you want in the same workbook.

Creating Recursive Lambda Function

Recursive Lambda is an advanced concept that allows you to create a lambda function and then call the same function from within itself.

It’s a pretty well-known concept in the programming world, and with Lambda you can bring the same concept in your worksheets as well.

Below is the video in which I’ve explained recursive lambda in detail (starts at 19 minutes and 20 seconds mark):

The recursive replacement example in this video uses a mapping table that stops at the first blank code. Keep the codes together without blank rows. SUBSTITUTE replaces matching text anywhere in a string, including inside longer words, and matches case.

This version uses OFFSET, which is volatile and recalculates whenever Excel recalculates. Too many recursive calls can also produce #NUM!.

For an ordinary batch of replacements, REDUCE with INDEX over a fixed mapping range is a simpler option in Microsoft 365 or Excel 2024. It avoids OFFSET and recursive calls. The video remains useful for learning how recursion works.

Since this is an advanced concept, I would request you to get used to the basic usage of lambda function first and then try a couple of scenarios where recursive lambda can be used.

Using LAMBDA Inside BYROW, MAP, and REDUCE

You don’t always need Name Manager to use LAMBDA. BYROW, MAP, and REDUCE take an unnamed LAMBDA as an argument and run it for you. You write the LAMBDA right inside the formula.

On the “BYROW and MAP” sheet, A2:A7 holds product IDs, and B2:D7 holds units sold in Jan, Feb, and Mar. Enter this in F2:

=BYROW(B2:D7,LAMBDA(r,MAX(r)))

This spills 515, 702, 340, 610, 260, and 830 into F2:F7. BYROW passes each row to the LAMBDA as r, and MAX returns that row’s highest monthly units.

To extract digits from every product ID, enter this in G2. It uses the same logic as our GetNum example:

=MAP(A2:A7,LAMBDA(id,TEXTJOIN("",TRUE,IFERROR(MID(id,SEQUENCE(LEN(id)),1)*1,""))))

This spills 231, 867, 567946, 2345, 3564, and 8473 as text into G2:G7. MAP passes one cell at a time.

Giving the whole range to =GetNum(A2:A7) returns one wrong value, “823”, without spilling. MAP lets you apply the calculation to each ID.

With LET, you can give this LAMBDA a local name. Enter this in H2:

=LET(GetDigits,LAMBDA(id,TEXTJOIN("",TRUE,IFERROR(MID(id,SEQUENCE(LEN(id)),1)*1,""))),MAP(A2:A7,GetDigits))

This returns the same six results. LET names the LAMBDA GetDigits for this formula only. Unlike a LAMBDA saved in Name Manager, that name doesn’t exist elsewhere in the workbook.

For the country-code replacements from the recursive example, enter this in B2 on the “REDUCE” sheet:

=REDUCE(A2:A12,SEQUENCE(ROWS(D2:D5)),LAMBDA(text,i,SUBSTITUTE(text,INDEX(D2:D5,i),INDEX(E2:E5,i))))

This spills the same 11 results as the recursive LAMBDA from the section above, including “Scott, United States” and “Steve, United States, United Kingdom”.

REDUCE starts with the names in A2:A12. For each mapping row i, it replaces code i with country i and carries the result to the next step, without recursion or OFFSET.

The mapping ranges are fixed. If you add another code, extend D2:D5 and E2:E5 in the formula.

Some Questions You May have about the Excel Lambda function

Below are some of the questions I’ve got from Excel users about this new Excel Lambda function:

Q. Can I create a Lambda function and use it in all my workbooks?

A: A LAMBDA name with Workbook scope belongs to that workbook. Creating it does not add a custom function to every Excel file on your computer.

If there is a lambda function that you need to reuse on another workbook, you will have to copy and create a named range for that lambda function in that workbook as well.

Q. In what versions of Excel is the Lambda function available?

A: LAMBDA is available in Excel for Microsoft 365 and Excel 2024 on Windows and Mac, and in Excel for the web. It is not available in Excel 2021, Excel 2019, or older versions.

Q. What is the benefit of creating a custom function using Lambda as compared to VBA?

A: The biggest benefit of using the lambda function to create a custom function is that you do not need to know any programming skills beforehand.

Also, unlike the VBA method, you do not need to save your files as macro-enabled files and enable any privacy or security settings to use these custom functions in the worksheet.

However, VBA, being a programming language, allows you to do a lot more than what can be achieved using a custom function created using Lambda.

I hope you found this article about the Excel Lambda function useful.

If you have any questions or suggestions for me, do let me know in the comments section.

Other Excel articles you may also like:

• Excel XLOOKUP Function
• 20 Advanced Excel Functions and Formulas
• Excel Filter Function
• INDEX & MATCH Functions Combo in Excel (10 Easy Examples)
• LET Function in Excel
• Excel REDUCE Function
• Excel MAP Function