Lambda function components

Let’s look at an example which puts the two earlier concepts together and creates a basic lambda function.

Suppose we have the following formula:

=LAMBDA(x, x+122)

Deconstructing this example and mapping it to the syntax definition, let’s look at x:

=LAMBDA(x, x+122)  

The first occurrence of x is defining the first and only parameter input to LAMBDA.

Now let’s look at x+122:

=LAMBDA(x, x+122)

The second parameter to LAMBDA, x+122, is the calculation, which makes use of the x name and will be substituted for the input value when the lambda function is called.

For example, suppose you called the lambda function and input the value 1 for x, Excel would substitute x for 1 and do the following calculation:

1 + 122

Which, as we all know:

1 + 122 = 123=

It’s almost as easy as… 1… 2… 3…

If you’ve been pasting the example above into Excel, you may have noticed some #CALC! errors. To resolve those, you’ll need to learn and perform the next step.

Calling a lambda function

To put it simply, you call a lambda function the same way you call native functions in Excel.

To illustrate this, let’s revisit the previous example and show how to call it with a value.

Uncalled

=LAMBDA(x, x+122) 

Called with the value 1

=LAMBDA(x, x+122)(1)

Returns

123

When should you call the lambda function?

  • You will want to return an uncalled lambda function when you initially store and name it, or for passing into other lambda functions which may subsequently call it.
  • And you should call the lambda function when you are making use of it during authoring and later re-use.

Naming a lambda function

Once you have authored a lambda function and are happy with the results, you should give it a name and store it for re-use.

To do this, you will want to make use of the Name Manager, so let’s dive in!

Name Manager Usage

To open Name Manager, click Formulas > Name Manager.

Naming a lambda function.

In the Name Manager dialog box, click the New button.

Naming a lambda function

In the New Name dialog box, enter the requested information, and then click OK.

Naming a lambda function.
  1. Name: The name of your function.
  2. Comment: A description and associated tooltip which will be shown when calling your function.
  3. Refers to: Your lambda function definition. 
Naming a lambda function.

And that’s it! Now, you can make use of your newly crafted custom function in the workbook by calling it by its name.

For example, we could call this lambda function by authoring a formula which calls MYLAMBDA

=MYLAMBDA(122)

Which would return the value

123