Google Sheets Function: LAMBDA
The LAMBDA function allows creating and returning a custom function.
It enables obtaining powerful combinations when used with certain other functions such as MAP and REDUCE.
Usage:
=LAMBDA(name, formula_expression)
or
=LAMBDA(name1, name2, ..., formula_expression)
Understanding this function
Let's start with something simple, the goal here will be to create a function that will double the quantity:
Enter into the LAMBDA function:
- name: the name of the variable (here, the number that needs to be passed as an argument and doubled by the function)
- formula_expression: the formula that will perform the calculation based on the name argument
The formula to double the quantity is:
=LAMBDA(quantity;quantity*2)The LAMBDA function has returned the created function, but since the quantity argument was not specified, it cannot return a result:
When you use other Google Sheets functions, you start by entering the function (for example SUM), then in parentheses the arguments.
With LAMBDA it's the same, enter the function (here LAMBDA(quantity;quantity*2)), then in parentheses the argument required by the function:
=LAMBDA(quantity;quantity*2)(A2)
Of course, the same calculation could have been done by entering =A2*2 but the goal was to start with a simple example.
=DOUBLE_QUANTITY(A2), what you need is the named functions functionality.What next?
Using the LAMBDA function alone does not offer much interest:
=LAMBDA(quantity;quantity*2)(A2)However, it becomes very useful when used with these other functions:
Staying with this same simple example (which doubles the quantity), using LAMBDA with the MAP function allows calculating the result of the function returned by LAMBDA for an entire range in a single formula:
=MAP(A2:A12;LAMBDA(quantity;quantity*2))