Concept
Availability: Microsoft 365, Excel 2024, Excel for the web (and Google Sheets with Named functions).
1. Syntax
=LAMBDA(parameter1, [parameter2, ...], calculation)
On its own in a cell it returns #CALC! — a LAMBDA must be called or named.
Test it by calling it immediately:
=LAMBDA(amount, rate, amount * (1 + rate))(1000, 18%)
→ 1,180.
2. Save it with a name
Formulas → Name Manager → New:
- Name:
WITHGST - Comment: "Amount including GST"
- Refers to:
=LAMBDA(amount, rate, amount * (1 + rate))
Now anywhere in the workbook: =WITHGST(B2, 18%). It works on arrays too: =WITHGST(tblSales[Amount], 18%) spills 16 results.
3. A useful one: Indian financial year label
Name FYOF, Refers to:
=LAMBDA(d, IF(MONTH(d) >= 4, "FY" & YEAR(d) & "-" & RIGHT(YEAR(d) + 1, 2), "FY" & YEAR(d) - 1 & "-" & RIGHT(YEAR(d), 2)))
=FYOF(DATE(2026,4,5)) → FY2026-27 · =FYOF(DATE(2026,1,15)) → FY2025-26.
The long FY formula from Module 2 is now one short, readable call — and if the rule changes, you fix it once in Name Manager.
4. LAMBDA + LET
=LAMBDA(amount,
LET(rate, IF(amount > 100000, 5%, 2%),
amount * rate))
Named COMMISSION: =COMMISSION(165000) → 8,250; =COMMISSION(45000) → 900.
5. Helper functions (preview)
| Function | Applies a LAMBDA to… | Example |
|---|---|---|
MAP |
each value | =MAP(tblSales[Amount], LAMBDA(a, COMMISSION(a))) → commission per order |
BYROW |
each row | =BYROW(B2:M5, LAMBDA(rowValues, MAX(rowValues))) → best month per region |
BYCOL |
each column | =BYCOL(B2:M5, LAMBDA(colValues, SUM(colValues))) |
SCAN |
running result | =SCAN(0, C2:C13, LAMBDA(acc, x, acc + x)) → running total |
REDUCE |
single final result | totals with custom logic |
=SUM(MAP(tblSales[Amount], LAMBDA(a, COMMISSION(a)))) gives the correct row-by-row commission — the problem calculated fields got wrong in Module 2.
6. Sharing LAMBDAs
Named LAMBDAs live in the workbook. To reuse them elsewhere, copy a sheet that uses them into the new workbook (names come along), or keep a template file. Write a Comment for each so others know what it does.
Common mistakes
Typing a LAMBDA in a cell without calling it (#CALC!). Parameter names that look like cell references. Building a LAMBDA when a simple formula would do.