Create your custom functions with LAMBDA

Reading time: 2 minutes
Last Updated on 25/08/2021 by Frédéric LE GUEN

With the version of Microsoft 365, you can create custom Excel functions with the LAMBDA function and named ranges.

Presentation of the LAMBDA function

The LAMBDA function was introduced in November 2020 for those who work with the Microsoft 365 version.

The LAMBDA function consists of a succession of variables, up to 253, which will be used by the expression which is necessarily the last argument of the function.

LAMBDA presentation

In this example, we have 2 variables, x and y, and the formula (or expression) x+y. When you call this function, you will pass as arguments the values for x and y and LAMDBA will return the result of the expression.

How to write a LAMBDA function

The problem with the LAMBDA function is the fact that you can't write it directly in a cell-like any other Excel function.

If you write LAMBDA in a cell, Excel will return an error #CALC!

LAMBDA isnt understood written in a cell

In fact, this function expects to receive parameters. So we have to find a trick to pass it the values ​​for the variables of the function.

Write the LAMBDA as a named range

The only way to use LAMBDA it's to write it as a named range Formulas > Define Name (or Name Manager)

Menu Formula Define Name
  1. Give a name to your custom function
  2. Then write your LAMBDA function in the Refers to box
Dialog box to create your LAMBDA function

Now, to check if your custom function is understood by Excel, write the symbol equal (like any other Excel function) and the name of your LAMBDA function

Your LAMBDA function appears in the list of Excel functions

And then, you have to pass the data expected by the custom function as a parameter

=MyCustomFunction(2,5)

Result of the LAMBDA function in a cell

Practical case of using the LAMBDA function

For example, you can create a custom LAMBDA function to calculate the Easter day like it is explain in this article

Custom EASTER function in action in Excel

Leave a Reply

Your email address will not be published. Required fields are marked *

Create your custom functions with LAMBDA

Reading time: 2 minutes
Last Updated on 25/08/2021 by Frédéric LE GUEN

With the version of Microsoft 365, you can create custom Excel functions with the LAMBDA function and named ranges.

Presentation of the LAMBDA function

The LAMBDA function was introduced in November 2020 for those who work with the Microsoft 365 version.

The LAMBDA function consists of a succession of variables, up to 253, which will be used by the expression which is necessarily the last argument of the function.

LAMBDA presentation

In this example, we have 2 variables, x and y, and the formula (or expression) x+y. When you call this function, you will pass as arguments the values for x and y and LAMDBA will return the result of the expression.

How to write a LAMBDA function

The problem with the LAMBDA function is the fact that you can't write it directly in a cell-like any other Excel function.

If you write LAMBDA in a cell, Excel will return an error #CALC!

LAMBDA isnt understood written in a cell

In fact, this function expects to receive parameters. So we have to find a trick to pass it the values ​​for the variables of the function.

Write the LAMBDA as a named range

The only way to use LAMBDA it's to write it as a named range Formulas > Define Name (or Name Manager)

Menu Formula Define Name
  1. Give a name to your custom function
  2. Then write your LAMBDA function in the Refers to box
Dialog box to create your LAMBDA function

Now, to check if your custom function is understood by Excel, write the symbol equal (like any other Excel function) and the name of your LAMBDA function

Your LAMBDA function appears in the list of Excel functions

And then, you have to pass the data expected by the custom function as a parameter

=MyCustomFunction(2,5)

Result of the LAMBDA function in a cell

Practical case of using the LAMBDA function

For example, you can create a custom LAMBDA function to calculate the Easter day like it is explain in this article

Custom EASTER function in action in Excel

Leave a Reply

Your email address will not be published. Required fields are marked *