LET and LAMBDA in Excel: Custom Functions Without VBA

What LET and LAMBDA do

• LET defines variables inside a formula: a value is calculated once and then reused by name.
• LAMBDA describes a function with parameters. Save it in Name Manager and you get your own workbook function, such as =MARGIN(B2,C2).
• MAP, REDUCE and BYROW take a LAMBDA and apply it to arrays — element by element, row by row or with an accumulator.

These functions are available in Microsoft 365 and Excel for the web; older perpetual versions may lack LAMBDA, in which case you will see #NAME?. Google Sheets supports all of them too.

The examples use commas as argument separators. In some regional settings Excel uses semicolons instead.

LET: a long formula becomes readable

A typical problem is the same fragment repeated several times in one formula. For example, margin per client:

=IF(SUMIFS(C:C,A:A,F2)=0,0,(SUMIFS(C:C,A:A,F2)-SUMIFS(D:D,A:A,F2))/SUMIFS(C:C,A:A,F2))

With LET:

=LET(
  rev,  SUMIFS(C:C, A:A, F2),
  cost, SUMIFS(D:D, A:A, F2),
  IF(rev = 0, 0, (rev - cost) / rev)
)

The benefits: each SUMIFS runs once, the logic is obvious, and changing one condition does not mean hunting for every copy. The last LET argument is always the final calculation.

LAMBDA: your own function in three steps

1. Write and test the LAMBDA in a cell by calling it immediately: =LAMBDA(rev, cost, IF(rev=0, 0, (rev-cost)/rev))(C2, D2)
2. Open Formulas → Name Manager → New, name it MARGIN and paste the formula without the trailing call into "Refers to".
3. Use it anywhere in the workbook: =MARGIN(C2, D2).

You can use LET inside a LAMBDA, and a named LAMBDA can call itself — that is how recursion works, for example to apply a list of text replacements one after another.

Entering a LAMBDA in a cell without calling it returns #CALC!. Passing the wrong number of arguments returns #VALUE!.

MAP, REDUCE and BYROW in practice

MAP applies a function to each element:

=MAP(A2:A100, LAMBDA(x, PROPER(TRIM(x))))

It trims spaces and fixes name capitalization for the whole column at once.

BYROW returns one value per row:

=BYROW(B2:E50, LAMBDA(r, MAX(r) - MIN(r)))

The spread of values in each row. The inner function must return a single value, not an array.

REDUCE accumulates a result:

=REDUCE(0, B2:B200, LAMBDA(acc, s, acc + IF(s > H1, s * H2, s * H3)))

Total commission where the rate depends on a threshold in H1 and the rates sit in H2 and H3. acc is the accumulator and 0 is its starting value. If you need running totals, use SCAN with the same logic.

Google Sheets equivalents

In Google Sheets a named function is created in a dedicated panel: you set the name, argument placeholders and the formula definition. You do not wrap it in LAMBDA — the arguments are used by name directly in the formula. Named functions can be imported from another spreadsheet.

Common mistakes

• Whole-column references inside MAP or BYROW — the function walks through a million empty rows and slows down. Limit the range or use tables.
• No description on a named function — six months later nobody remembers what it does. Fill in the comment field.
• Complex logic without testing in parts. Debug the LAMBDA with a direct call in a cell first, then save it.
• Expecting the function in every file. A named LAMBDA lives inside one workbook; to reuse it, copy a sheet across or use the Excel Labs add-in.