ID EN
…
#Dynamic array

LAMBDA

Excel Functions

Create custom function

Syntax

EXCEL 
=LAMBDA(parameter, ..., calculation)

Arguments

Parameter Description
parameter An input value for the function.
calculation The calculation to perform as the result of the function. Must be the last argument.

Return Value

As defined by formula

Details

The LAMBDA function provides a way to create a custom function in Excel. Once defined and named, a LAMBDA function can be used anywhere in a workbook. LAMBDA functions can be very simple or quite complex, stringing together many Excel functions into one formula. A custom LAMBDA function does not require VBA or macros. Example 1 | Example 2 | Example 3 | Example 4 In computer programming, the term "lambda" refers to an anonymous function or expression. An anonymous function is a function defined without a name. In Excel, the LAMBDA function is first used to create a generic (unnamed) formula. Once a generic version has been created and tested, it is ported to the Name Manager, where it is formally defined and named. One of the key benefits of a custom LAMBDA function is that the logic conta

Examples

Example 1 - basic example

To illustrate how LAMBDA works, let's begin with a very simple formula:

EXCEL 
=x*y // multiply x and y
Example 1 - basic example

In Excel, this formula would typically use cell references like this:

EXCEL 
=B5*C5 // with cell references
Example 1 - basic example

As you can see, the formula works fine, so we are ready to move on to creating a generic LAMBDA formula (unnamed version). The first thing to consider

EXCEL 
=LAMBDA(x,y  // begin with input parameters
Example 1 - basic example

Next, we need to add the actual calculation, x*y:

EXCEL 
=LAMBDA(x,y,x*y)
Example 1 - basic example

If you enter the formula at this point, you'll get a #CALC! error. This happens because the formula has no input values to work with since there are n

EXCEL 
=LAMBDA(x,y,x*y)(B5,C5) // testing syntax
Example 1 - basic example

Make sure the formula begins with an equals sign (=). Now that the LAMBDA formula has a name, it can be used in the workbook like any other function.

EXCEL 
=XBYY(B5,C5)

See Also

LAMBDA LET ISOMITTED MAP SCAN REDUCE MAKEARRAY BYCOL