Excel functions / Logical
LAMBDA function
Makes a function of your own: names for its inputs, then the calculation.
Syntax
=LAMBDA(parameter1, [parameter2], …, calculation)
Arguments
parameter: A name for an input, such as x.calculation: The formula to run, using those names. It always comes last.
When to use it
LAMBDA turns a calculation into a function of your own. You name its inputs, then write the calculation with those names: LAMBDA(net, rate, net*(1+rate)) is a function that adds tax to a price.
A LAMBDA does nothing until it is given values. There are three ways to do that: put them in brackets straight after it, give it a name with LET and call that name, or hand it to a function such as MAP, REDUCE or BYROW, which calls it once for each item.
Examples
Try a LAMBDA by giving it values straight away
A B C D 1 Net VAT rate 2 200 20% 240 In D2:
=LAMBDA(net, rate, net*(1+rate))(A2, B2)→ 240Name it with LET, then use it more than once
A B C D 1 Width Height 2 3 4 16 In D2:
=LET(area, LAMBDA(w, h, w*h), area(A2, B2) + area(2, 2))→ 16One calculation for every row
A B C 1 Net 2 100 120 3 250 300 4 40 48 In C2:
=MAP(A2:A4, LAMBDA(net, net*1.2))→ 120; 300; 48
Common mistakes
- #CALC! from a LAMBDA on its own.
=LAMBDA(x, x*2)is a function with nothing to work on. Give it a value,=LAMBDA(x, x*2)(4), or pass it to MAP, REDUCE, SCAN, BYROW or BYCOL. - Input names that are cell addresses. A name such as
x1ortax2is read as a cell. Use names with no digits at the end:x,rate,total. - Too many values. A LAMBDA with two inputs takes at most two values. Giving it three is #VALUE!.
In Excel you can save a LAMBDA under a name in Formulas → Name Manager, then use it anywhere in the workbook like a built-in function. This site has no saved names, so name it inside the formula with LET.
Common questions
What is the LAMBDA function in Excel?
LAMBDA lets you write your own function with a formula, without VBA. You list the inputs and then the calculation, for example =LAMBDA(x, x*2), and save it under a name or use it inside MAP, REDUCE and BYROW.
Practise it
Related functions
- LET: Names intermediate results so a formula is easier to read.
- MAP: Runs a function on each value of an array and returns the results in the same shape.
- REDUCE: Boils an array down to one result by running a function on a running value and each item in turn.
- BYROW: Runs a function on each row of an array and returns one result per row.
- SCAN: Like REDUCE, but returns every step: a running total, a running maximum and the like.