The Excel Lambda Function is a new powerful addition to the Excel suite of functions, that Microsoft announced not long ago.

The Lambda function allows you to define a new Excel function with one or multiple parameters. I see it being especially useful in case of string manipulation or definition of new mathematics/statistical functions. What Lambda functions will not make any easier – are nested If statements or very complex calculations requiring loops etc. If you want to learn how to create such complex custom Excel function see the end of the article.

Excel Lambda definition

The Lambda function consists of only 2 types of arguments.

• Parameters – one or multiple parameters that you need to provide. Each one needs to have a different label as they will be uniquely used in the function
• Calculation – the actual results of the usage of the parameter. This will be returned as the result of the new created function

Below you can find the most simple example of defining and using a Lambda function in Excel.

Simple right? Notice that to run the function we had to append the brackets ( ) and enter the parameters after the lambda function. At first glace it is hard to appreciate the value of lambda as it does not seem like it can save us any time. Especially as we need to define it each time. This is where Named Ranges can help us…

Lambda as Named Range

To be able to reuse a Lambda function as you might any other function in Excel we need to name it as a Named Range. For this we will need to use the Name Manager in Excel.

Open the Name Manager

Go to Formulas ribbon and click on Name Manager in the Defined Names group.

Create a new Named Range and Paste the Lambda function

Click the New... button to create a new Named Range. Now enter the definition of your Lambda function.

Remeber to omit the ( ) – calling of the function. We will use the Named Range to call the function later.

Use the Named Range as your Lambda

You can now easily use the Lambda function without having to define it each time:

'Excel formula
=ADDFUNC(1,2)
'The result
3

Did you know that when you copy a worksheet to another workbook your Named Ranges will copy as well? This is great way to copy a lot of custom formulas when using Lambda function in Excel. My suggestion is to keep you custom Lambda functions handy in a separate workbook and pulling them in when needed.

Excel Lambda Use Cases

I am sure as Lambda will become more widely available we will see more interesting usage examples. I had some of my own ideas on how I will start using the Excel Lambda function. See examples below:

Find the Hypotenuse of a right angle

'Definition
=LAMBDA(a, b, SQRT((a^2+b^2)))

'Usage
=MYLAMBDA(3,4)
'Result
5

Get Email domain from String

I think the Lambda function is going to be a great time saver for working with strings. Below an example of a function that helps get the domain from an email.

'Definition
=LAMBDA(mail,RIGHT(mail,LEN(mail)-FIND("@",mail)))

'Usage
=MYLAMBDA("something@gmail.com")
'Result
gmail.com

Recursion using Excel Lambda

One of the magics of using the Lambda function is that it allows for recursion. Recursion is (in simple terms) the ability of a function to call itself. The function would usually keep calling itself recursively until a certain conditional is fulfilled.

Let us use the example of the factorial function represented by the Excel FACT function. Given a number it returns the multiplication of the number and all following substracted numbers until 1. See example below:

'Factorial of 5
5! = 5 * 4 * 3 * 2 * 1
'Result
120

Sample example in Excel

=FACT(5)
'Result
120

Now let us recreate this function using Lambda and recursion. I am assuming I have defined my Lambda as a Named Range MYFACT.

Lambda vs VBA UDF

Lambda is a new addition to the Excel function family. But many of us don’t yet have access to it. Similarly in some cases we might still stumble on very complex functions with which Lambda might not be the easiest option. Fortunately since all of us can also use something called as UDF i.e. User Defined Function.

What are UDF’s? A User Defined Function is basically a Excel VBA Macro that can be made public to be used similarly as a custom Excel function. To define a UDF follow these simple steps:

1. Make the Developer ribbon visible
2. Create a new VBA Module by right-clicking in the Project window
3. Create a VBA Function and make sure it is Public
4. Save your file as XLSM file to save your macros

See the example below:

Now you can use the function just like you would with the Lambda function:

If you want below you can copy the MYFACT VBA function:

Public Function MYFACT(num As Long)
    If num = 1 Then
        MYFACT = 1
    Else
        MYFACT = num * MYFACT(num - 1)
    End If
End Function

How to enable Lambda in Excel?

Most of you are probably scratching your heads trying to figure out how to enable this cool new Excel function. Well Microsoft is not eager to roll-out it out and sometimes your own organization may be at fault. Below message from Microsoft official page:

Note: This feature is only available if you have an Office 365 subscription. If you are an Office 365 subscriber, make sure you have the latest version of Office.

If you have a desktop version of Excel 2010/2016 basically no Lambda for you buddy. However if you have Office 365 and can’t find the function – reach out to your IT Admin to check when they will be releasing the newest updates within your organization.

Otherwise I suggest you can still you Excel UDF functions to create custom functions.

Conclusions

The Lambda function is a great addition and was definitely missing from Excel. As with all new functions I feel some time will need to pass for it to be widely used as not all Excel versions will have it enabled (Excel 2016 and older will not).

For now I recommend for all complex Excel function examples to resort to VBA UDF functions. At least these can be ported to almost any version of Excel and debugging is much easier.

What is your opinion?