LAMBDA Function

The LAMBDA function in Excel is a new function currently only available to beta users, and arriving soon to all Microsoft 365 users.

This is a very special function as it enables you to create your own function in Excel. You can assign a name to this function and then use it like any of your other favourite functions.

This function is available throughout the workbook. And only in this workbook.

In this tutorial, we will explain everything you need to know using two LAMBDA function examples.

How to use the LAMBDA Function in Excel

To create a LAMBDA function, we will follow this process;

• Write the formula and test that it works.
• Create the LAMBDA function for the formula.
• Test the LAMBDA function
• Assign a name and description to the function.

The syntax for the LAMBDA function is;

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

Parameter: This is a value to be used in the formula. It can be a cell reference, text string or numeric value. You can have up to 253 parameters. This is an optional argument.

Think of the lookup value in a VLOOKUP function, or the number range for a SUM function. These are examples of parameters in other functions. A parameter is used to accept an input from the user.

Calculation: The formula to be executed by your LAMBDA.

Create your Excel LAMBDA Function

For our first LAMBDA function example, we will create a function to count the number of words in a cell.

Surprisingly, there is not a function in Excel to do this. But with LAMBDA, we can create our own.

We have the following range to create and test our LAMBDA function in Excel.

The formula to count the number of words in a cell is;

=LEN(TRIM(A2))-LEN(SUBSTITUTE(TRIM(A2),” “,””))+1

Let’s create the following LAMBDA function.

=LAMBDA(text, 
LEN(TRIM(text))-LEN(SUBSTITUTE(TRIM(text)," ",""))+1)

In this LAMBDA function, one parameter is used. This parameter is named text and you can see it has been passed to the formula twice.

Test your LAMBDA

To test your LAMBDA, simply enter the parameters to test in brackets after your function. For example, (A2). This is known as calling the LAMBDA function.

When you enter a LAMBDA function in a cell, you must call the function or it will return a #CALC! error.

The following formula is passed the value from cell A2 and successfully returns a result.

=LAMBDA(text,
LEN(text)-LEN(SUBSTITUTE(text," ",""))+1)(A2)

Great news!

We can now assign a name for our function.

Assign a Name to your Function

To assign a name to a function, it is added to the Name Manager.

We will name this function COUNTWORDS and also provide a neat description for users to see when entering it on a worksheet.

1. Copy your formula from the cell (without the call at the end).
2. Click Formulas > Name Manager.
3. Click New.
4. Type a Name for your LAMBDA function, a description and paste the formula into the Refers to field.

Let’s now see it in action on the worksheet.

As you type in your function, you see its name appear in the Intellisense list and also its description.

The function works brilliantly.

LAMBDA Function Example with Two Parameters

For this LAMBDA function example, we will create a function to extract the text between two characters in Excel.

We will use this range to create and test the Excel LAMBDA function.

The formula we will use to extract text between two characters is;

=LEFT(MID(A2,FIND("/",A2)+1,
LEN(A2)),FIND("/",MID(A2,FIND("/",A2)+1,LEN(A2)))-1)

Let’s create the LAMBDA function for this, and call it to test that it works.

This LAMBDA has two parameters – text and delimiter. When calling the LAMBDA function, A2 has been passed for the text parameter and “/” for the delimiter.

=LAMBDA(text,delimiter,
LEFT(MID(text,FIND(delimiter,text)+1,
LEN(text)),FIND(delimiter,MID(text,FIND(delimiter,text)+1,LEN(text)))-1))(A2,"/")

It works! This will greatly simplify the function for us and others.

We will name this function TEXTBETWEEN.

In this example, we will add a definition for the two parameters the function uses. This is good practise to assist the user.

I have used two pipe symbols “||” to separate the parameter definitions.

Ok, here is the fun part. Writing it to the worksheet for the first time.

You can see the parameter definitions appear to help the user write the function.

The function is working nicely.

The LAMBDA function in Excel is very different to Excel functions that have come before it.

It is a very exciting time. What functions will you create with LAMBDA?