Updated: Feb 20
Lambda function is a newly developed function in MS Excel under the Office Insider, Beta Channel. Beta Channel, formerly called the Insider Channel (or Insider Fast), is best suited for those who want to use the latest developed tools and functions in MS Excel to identify issues and provide feedback about these new features still in development to Microsoft Office. To be able to use these functions, one would have to ensure that under Account/Office Insider, the user is signed up for the Beta Channel. Ensure that the MS Excel is up to date and updated automatically as new downloads become available for the subscription-based version of Microsoft Suite.
Lambda function is used to create custom, reusable functions and enables the user to call them by a desirable name. The new function is available throughout the workbook and is called just like other native Excel functions.
The syntax of the function is the following: =LAMBDA([parameter1, parameter2, …,] calculation)
In the above syntax, Parameters are values that are to be passed to the function. This includes cells references, strings, numbers, or arrays. Up to 253 parameters can be inputted in the formula. This argument is optional.
In the above syntax, Calculation is the formula to be executed throughout the workbook and returns the function. It must be the last argument and it must return a result. This argument is mandatory.
To create the Lambda formula, it is best to follow a step-by-step process to help make sure the Lambda works as intended and closely resembles the behavior of a native Excel function. There are three steps to follow:
1) Test the Formula
2) Create the Lambda in a Cell
3) Pass the Lambda to the Name Manager
The following guidelines apply to writing proper Lambda function in MS Excel:
If more than 253 parameters are inputted, Excel returns a #VALUE! error.
If an incorrect number of arguments is passed to a LAMBDA function, Excel returns a #VALUE! error.
If a LAMBDA function is called from within itself and the call is circular, Excel returns a #NUM! error.
If a LAMBDA function is created in a cell without also calling it from within the cell, Excel returns a #CALC! error.
Lambda names and parameters follow the Excel syntax rules for names, with one exception: don't use a period (.) in a parameter name. For more information, see Names in formulas.
Follow best practices when creating a LAMBDA function as done with any native Excel formula, such as passing of the correct number and type of arguments, matching open and close parentheses, and entering numbers as unformatted. Also, the Evaluate command is used, Excel immediately returns the result of the LAMBDA function and we can't step into it. For more information, see Detect errors in formulas.
The following video demonstrates the three simple steps to creating proper Lambda functions through two simple examples.