LET and LAMBDA Functions in Excel

The LET function in Excel lets you name intermediate calculations inside a single formula, which makes long formulas readable and can speed them up by computing a value once. LAMBDA goes further and lets you define your own reusable function from a formula, with named arguments. Both are available in Microsoft 365 and Excel 2021 and later. They turn dense one liners into something you can actually maintain.

How LET and LAMBDA work

=LET(name1, value1, name2, value2, ..., calculation) defines one or more names, assigns each a value, and then uses those names in a final calculation. You list name and value pairs, and the last argument is the formula that returns the result.

Naming a subexpression means Excel evaluates it once and reuses it, rather than recomputing the same lookup several times. That improves both readability and speed in formulas that repeat a calculation.

=LAMBDA(param1, param2, ..., calculation) defines a function. On its own you call it by following it with arguments in parentheses, but the real use is to put a LAMBDA in the Name Manager under a name like MarginPct, after which =MarginPct(revenue, cost) works like any built in function across the workbook.

Worked example: a readable margin formula

Suppose revenue is in B1 and cost is in B2, and you want gross margin as a percent without repeating the subtraction.

1. Without LET you might write =(B1-B2)/B1, which recomputes B1 and is hard to extend.
2. With LET write =LET(rev, B1, profit, B1-B2, profit/rev). The name rev holds revenue, profit holds the difference, and the result is profit divided by rev.
3. If revenue is 1000 and cost is 700, profit is 300 and the formula returns 0.3, or 30 percent.
4. To reuse this everywhere, open Name Manager and define MarginPct as =LAMBDA(rev, cost, (rev-cost)/rev).
5. Now =MarginPct(B1, B2) returns 0.3 from any cell, and =MarginPct(2000, 1500) returns 0.25.

LET names the steps; LAMBDA turns the same logic into a reusable named function.

How analysts use them in models

LET and LAMBDA bring software style structure to spreadsheet formulas.

• Use LET to name intermediate results in a long formula so a reviewer can read the logic top to bottom.
• Use LET to compute an expensive lookup once and reuse the name, trimming recalculation time on big sheets.
• Use LAMBDA to package a recurring calculation, like a tax or margin rule, into one named function used everywhere.
• Centralize a LAMBDA in the Name Manager so a single edit updates every cell that calls it.
• Combine LAMBDA with helper functions such as MAP or REDUCE to apply custom logic across a whole range.

Pitfalls to watch

Both functions require Microsoft 365 or Excel 2021 and later. A LET or LAMBDA formula opened in Excel 2019 or earlier shows a _xlfn error and does not calculate, so confirm the version your file will be used in.

Inside LET the order matters. A name must be defined before it is used, so =LET(a, b+1, b, 5, a) fails because b is referenced before it is assigned. List your name value pairs in dependency order.

A LAMBDA stored in the Name Manager is easy to forget about, since the logic is no longer visible in the cell. Document what each named function does, because a reviewer sees only =MarginPct(B1, B2) and not the formula behind it.