Create your custom functions with LAMBDA

Create your custom functions with LAMBDA
Last Updated on 11/05/2024
Reading time: 2 minutes

With 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 that is necessarily the function's last argument.

=LAMBDA(x, y, x+y)

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 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

This function expects to receive arguments. So, we have to find a trick to pass 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 explained 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 11/05/2024

With 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 that is necessarily the function's last argument.

=LAMBDA(x, y, x+y)

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 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

This function expects to receive arguments. So, we have to find a trick to pass 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 explained in this article

Custom EASTER function in action in Excel

Leave a Reply

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