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!
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)
- Give a name to your custom function
- Then write your LAMBDA function in the Refers to box
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
And then, you have to pass the data expected by the custom function as a parameter
=MyCustomFunction(2,5)
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
