How to use LET and LAMBDA in Excel and Google Sheets to make long formulas shorter, faster and easier to read, create your own reusable functions, and use helpers like MAP, BYROW and REDUCE, with worked examples.
Long formulas that repeat the same calculation several times are hard to read and easy to break. LET and LAMBDA fix that. LET lets you name parts of a formula, and LAMBDA lets you create your own reusable functions. Both are available in Microsoft 365 and recent versions of Excel, and in Google Sheets.
LET: name values inside a formula
LET takes pairs of names and values, followed by a final calculation:
=LET(name1, value1, name2, value2, calculation)
Example: a discount with a minimum
Without LET, you might repeat a calculation:
=IF(B2*C2*0.9 < 50, 50, B2*C2*0.9)
With LET:
=LET(total, B2*C2*0.9, IF(total < 50, 50, total))
The calculation is done once, the formula is clearer, and changing it means editing one place. Here, MAX(50, B2*C2*0.9) would also work; LET shines in longer formulas.
Example: cleaning and counting
=LET(clean, TRIM(A2:A100), COUNTA(UNIQUE(FILTER(clean, clean<>""))))
This counts unique, non-blank names after trimming spaces.
LAMBDA: build your own function
LAMBDA defines parameters and a calculation:
=LAMBDA(price, rate, price*(1+rate))
On its own, that returns an error, because it's a function waiting for inputs. You can test it by adding the inputs straight after: =LAMBDA(price, rate, price*(1+rate))(100, 0.2) returns 120.
Save it with a name
- Excel: go to Formulas, then Name Manager, then New. Name it, for example
ADDVAT, and paste the LAMBDA into "Refers to". Now=ADDVAT(B2, 0.2)works anywhere in the workbook. - Google Sheets: go to Data, then Named functions, then Add new function, and enter the parameters and formula. Named functions can be imported into other spreadsheets.
Helper functions
LAMBDA powers a set of helper functions that apply a calculation across arrays:
- MAP: applies a LAMBDA to each value.
=MAP(A2:A10, LAMBDA(x, PROPER(TRIM(x))))tidies a list of names. - BYROW / BYCOL: return one result per row or column.
=BYROW(B2:E10, LAMBDA(r, MAX(r)))gives each row's maximum. - REDUCE: accumulates a single result, such as a running calculation.
- SCAN: like REDUCE but returns each step, such as running totals:
=SCAN(0, B2:B13, LAMBDA(acc, x, acc + x)). - MAKEARRAY: builds an array of a given size from a calculation.
Worked example: a safe percentage change
Percentage change formulas fail with a #DIV/0! error when the old value is zero. A named LAMBDA handles it once:
PCTCHANGE = LAMBDA(old, new, IF(old=0, "", (new-old)/old))
Then =PCTCHANGE(B2, C2) works down a column, and combined with MAP it works on a whole range: =MAP(B2:B20, C2:C20, LAMBDA(o, n, PCTCHANGE(o, n))). Format the results as percentages.
Debugging LET formulas
If a LET formula returns the wrong answer, temporarily replace the final calculation with one of the names, for example =LET(total, B2*C2*0.9, total), to see the intermediate value. Work through each name until you find the step that's wrong, then put the final calculation back.
Good practice
- Use clear, descriptive names, such as
vat_raterather thanx. - Add a description in Name Manager or the named function dialog so others understand it.
- Test with simple inputs before using it widely.
- Remember compatibility: older Excel versions don't support LET or LAMBDA and will show errors.
When not to use them?
For a simple one-off formula, LET adds little. Custom LAMBDA functions can confuse colleagues unfamiliar with them, so document them. For complex logic used across many workbooks, a proper add-in or script may be more maintainable.
Frequently asked questions
Does LET make formulas faster?
It can, because a named calculation is evaluated once rather than repeatedly.
Can a LAMBDA call itself?
Yes, recursion is possible with named LAMBDAs, but use it carefully to avoid errors.
Sources and further reading
- Microsoft Support: LET function
- Microsoft Support: LAMBDA function
- Google Docs Editors Help: LAMBDA function
- Google Docs Editors Help: Named functions
Facts checked on 20 October 2026. Spotted an error? Tell us and we will correct it.