LAMBDA and LET in Excel: Write Your Own Functions

LET and LAMBDA let you write cleaner, reusable Excel formulas. LET assigns names to values inside a formula so you calculate things once and read them like plain English; LAMBDA turns any formula into your own custom function—like =MARGIN(B2, C2)—that you can reuse across the workbook without VBA or macros.

Why these two functions matter

Most analysts eventually write a formula that's ten lines long, repeats the same SUMIFS three times, and is impossible to debug a month later. LET and LAMBDA fix both problems. LET removes repetition and adds readable names. LAMBDA packages logic so it lives in one place: fix it once and every cell that uses it updates.

LET is available in Excel for Microsoft 365, Excel for the web and Excel 2021 or later. LAMBDA and its helper functions (BYROW, BYCOL, MAP, REDUCE, SCAN, MAKEARRAY, ISOMITTED) are in Microsoft 365 and Excel for the web, and have been added to newer perpetual versions. Because availability changes, check Microsoft's support page for each function if one returns #NAME?.

LET: name your calculations

LET takes pairs of names and values, followed by a final calculation that uses those names:

=LET(name1, value1, [name2, value2, ...], calculation)

Here's a status message comparing year-to-date sales to a target in D1, written without LET:

=IF(SUM(E2:E500)>=D1, "On track", "Behind by " & TEXT(D1-SUM(E2:E500), "#,##0"))

And with LET:

=LET(
    total,  SUM(E2:E500),
    target, D1,
    gap,    target - total,
    IF(gap <= 0, "On track", "Behind by " & TEXT(gap, "#,##0"))
)

The sum is calculated once, every piece has a name, and the last line reads like a sentence. Press Alt+Enter inside the formula bar to add line breaks, and widen the formula bar (Ctrl+Shift+U) to see them.

LET with dynamic arrays

LET shines when combined with dynamic array functions, because you can store an intermediate array once and reuse it:

=LET(
    east,   FILTER(A2:E500, C2:C500="East"),
    sorted, SORT(east, 5, -1),
    TAKE(sorted, 5)
)

This returns the five largest East-region sales. If FILTER, SORT and TAKE are new to you, see our guide to Excel dynamic arrays.

LAMBDA: create your own functions

LAMBDA defines parameters and a calculation:

=LAMBDA(parameter1, [parameter2, ...], calculation)

On its own in a cell, a LAMBDA returns a #CALC! error because nothing has been passed in. You can test it by adding the arguments in parentheses right after it:

=LAMBDA(price, cost, IF(price=0, 0, (price-cost)/price))(120, 90)

That returns 0.25 (a 25% margin). Once it works, give it a name.

Saving a LAMBDA as a named function

1. Go to Formulas > Name Manager > New (or Formulas > Define Name).
2. In Name, type MARGIN.
3. Add a Comment such as "Gross margin %: (price - cost) / price". It appears as a tooltip when you type the function.
4. In Refers to, paste =LAMBDA(price, cost, IF(price=0, 0, (price-cost)/price)) without the test arguments.
5. Click OK. Now type =MARGIN(B2, C2) anywhere in the workbook.

Named LAMBDAs are saved inside the workbook. To use one in another file, copy a sheet that uses it into the new workbook, or recreate the name. Microsoft's free Excel Labs add-in also offers an editor for managing and sharing LAMBDA functions; check its current status in the Office add-ins store.

Optional parameters

Wrap a parameter name in square brackets to make it optional, and use ISOMITTED to supply a default:

FISCALQ =LAMBDA(d, [start_month],
    LET(
        s, IF(ISOMITTED(start_month), 10, start_month),
        m, MOD(MONTH(d) - s, 12),
        "Q" & (INT(m / 3) + 1)
    )
)

=FISCALQ(A2) returns the fiscal quarter for a year starting in October (October–December is Q1); =FISCALQ(A2, 7) uses a July start instead.

The LAMBDA helper functions

These functions take a LAMBDA as an argument and apply it across a range, unlocking calculations that used to require helper columns or VBA.

The SCAN example produces a running total for twelve months in one formula. The BYROW example totals each row of a 12-month grid, which is ideal for a monthly sales matrix by store. The REDUCE example adds up only the positive values and returns a single number.

Notice that the helper functions don't require a named LAMBDA. You can write the LAMBDA inline for one-off logic, or pass a named function you already defined, as the MAP example does with MARGIN. Inline is fine for quick work; naming is better when the same logic shows up in several places.

Practical LAMBDAs for analysts

A few custom functions that pay off quickly in business workbooks:

PCTCHANGE =LAMBDA(new, old, IF(old=0, "", new/old - 1))

CLEANNAME =LAMBDA(txt, PROPER(TRIM(txt)))

ATTAIN    =LAMBDA(actual, target, IF(target=0, "", actual/target))

STATUS    =LAMBDA(actual, target, [tolerance],
    LET(
        t,   IF(ISOMITTED(tolerance), 0.9, tolerance),
        pct, actual / target,
        IF(pct >= 1, "On track", IF(pct >= t, "At risk", "Behind"))
    )
)

Use them with MAP or BYROW to process whole columns, for example =MAP(Sales[Actual], Sales[Target], LAMBDA(a, t, STATUS(a, t))). Functions like CLEANNAME fit naturally into the routine in our Excel data cleaning checklist.

Best practices and pitfalls

• Name things clearly. Use descriptive parameter names (price, cost) rather than x and y, except in very short helper LAMBDAs.
• Avoid names that look like cell references. Names such as AB1 or R1C1 aren't allowed; pick words.
• Document with comments. The Name Manager comment is your user guide; fill it in.
• Test before naming. Use the inline call pattern =LAMBDA(...)(args) to check results first.
• Mind compatibility. Colleagues on older Excel versions will see #NAME?. If you share files widely, confirm their version.
• Don't over-engineer. A short formula that everyone understands beats a clever LAMBDA nobody can maintain. Use LAMBDA for logic you repeat often.

LAMBDAs can even call themselves by name to perform recursion, but that's an advanced technique; most business needs are covered by LET, a few named functions and the helper functions above.

Next steps

Find the longest formula in a workbook you maintain and rewrite it with LET. Then pick one calculation you repeat across sheets—margin, attainment or a status label—and save it as a named LAMBDA with a comment. Once that feels comfortable, try BYROW and SCAN on a monthly sales grid. If you're also moving toward Power BI, the same "name it once, reuse everywhere" mindset is exactly how measures work in DAX.

Watch: video lessons

▶

▶

Frequently asked questions