LET and LAMBDA in Excel

In short: =LET(net,B2*B3,net*1.08) stores B2*B3 under the name net and keeps calculating with it. =LAMBDA(x,x*1.08)(B2) is a tiny custom function with the parameter x, called right away with B2. LET needs Excel 2021 or later, LAMBDA needs Excel 2024 or Microsoft 365.

Try it now

An order with quantity, unit price, discount and sales tax. Without LET you would type the net amount into the formula twice. With LET you write it once and give it a name.

Task In B7, use LET to calculate the order total including tax. First define a name net for quantity times unit price minus the discount, then calculate net plus net times the tax rate.

	A	B
1	Input	Value
2	Quantity	40
3	Unit price $	12.5
4	Discount	0.1
5	Sales tax rate	0.19
6
7	Total incl. tax

The LET function syntax

Argument	What it means	In the example
name1	A name you choose. It must start with a letter.	net
name_value1	The value or calculation stored under that name	B2*B3*(1-B4)
more pairs (optional)	name2, value2 and so on, up to 126 pairs according to Microsoft. Later values may use earlier names.	none
calculation	The last argument. Its result is what the cell shows.	net+net*B5

LET pays off in two ways. The formula becomes readable, because net tells you more than B2*B3*(1-B4). And Excel calculates that part once, no matter how often the name appears. In long formulas with a lookup or FILTER inside, that saves real calculation time.

Several names in a row read like a small worksheet inside one cell:

=LET(net,B2*B3*(1-B4),tax,net*B5,net+tax)

The LAMBDA function: build your own

LAMBDA goes one step further. Instead of a fixed value it stores a recipe with placeholders called parameters. The syntax is LAMBDA(parameter1, parameter2, …, calculation). The last argument is always the calculation, everything before it is a parameter, up to 253 of them.

A LAMBDA on its own does not calculate anything. Type just =LAMBDA(old,new,(new-old)/old) into a cell and Excel shows #CALC!. To test it, add the values in a second pair of parentheses straight after it. That is the next exercise.

Practice: test a LAMBDA in a cell

Regional sales for last year and this year. The change in percent is (new minus old) divided by old. Turn that into a LAMBDA and call it with the values in each row.

Task Before you save your own function in Name Manager, you test it in a cell. In D2, write a LAMBDA with the parameters old and new that calculates (new-old)/old, and call it right away with B2 and C2. The formula is filled down to D6.

	A	B	C	D
1	Region	Last year $	This year $	Change
2	North	48000	52800
3	South	61500	59040
4	West	39800	43780
5	East	52000	57200
6	Central	44700	44700

This exercise is part of Premium.

Premium unlocks every exercise in every lesson, unlimited practice with fresh tasks and the final test with certificate.

See Premium Already Premium? Log in

The first exercise in this lesson is free.

Naming a LAMBDA: Name Manager or LET

LAMBDA becomes really useful once the function has a name. There are two ways to give it one.

In Name Manager, for the whole workbook

Once the LAMBDA works in a cell, open Formulas > Name Manager > New. Enter a name such as PCTCHANGE, and under Refers to paste the LAMBDA without the call: =LAMBDA(old,new,(new-old)/old). From then on, any cell in the workbook can use =PCTCHANGE(B2,C2) like a built-in function. The Comment field lets you describe what it does, and that text shows up as a tooltip while typing. On a Mac, Microsoft points you to Formulas > Define Name instead. The function belongs to the workbook, so another file only has it once you define it there too.

Inside LET, for a single formula

If you only need the function in one formula, but several times, define it with LET:

=LET(pctchange,LAMBDA(old,new,(new-old)/old),MAX(pctchange(B2,C2),pctchange(B3,C3)))

This returns the larger of two growth rates. The recipe appears only once, so a change to it happens in one place.

Common errors

#CALC! with LAMBDA

The LAMBDA sits in a cell without being called. Add the values in parentheses, (B2,C2), or save it in Name Manager.

#VALUE! when calling it

The number of values in the call does not match the number of parameters. A LAMBDA with old and new needs exactly two values.

#NAME? with LET

The name is invalid or misspelled. Names must start with a letter and must not look like a cell reference, so a1 or abc1 will not work. Microsoft also notes that c is invalid because it clashes with R1C1 notation, so stick to descriptive names. If #NAME? shows up in an older version of Excel, that version simply does not know LET or LAMBDA. More on error messages in error values.

The final calculation is missing

LET needs an odd number of arguments: name and value pairs, then the calculation. Leave out the calculation and Excel refuses the formula with a message that there is a problem with it.

LET and LAMBDA in German Excel

Both functions keep their names in German Excel. What changes are the separators: semicolons between arguments and a comma as the decimal mark.

English	German
=LET(net,B2*B3,net*1.08)	=LET(netto;B2*B3;netto*1,08)
=LAMBDA(old,new,(new-old)/old)(B2,C2)	=LAMBDA(alt;neu;(neu-alt)/alt)(B2;C2)
Name Manager, Refers to	Namens-Manager, Bezieht sich auf
#CALC!	#KALK!

Which Excel versions have LET and LAMBDA?

According to Microsoft, LET is available in Microsoft 365, Excel 2024 and Excel 2021, each including the Mac version. LAMBDA is available in Microsoft 365 and Excel 2024, also on Mac, but not in Excel 2021. Excel 2016 and 2019 have neither. Open a workbook that uses them in one of those versions and the cells show #NAME?.

FAQ