LET vs LAMBDA in Excel: Name a Value or Build a Function

LET vs LAMBDA in Excel comes down to one question: do you want to tidy one formula, or reuse the same logic in many places? LET names values inside a single formula so each one is calculated once. LAMBDA turns a formula into your own function, with inputs, that you can call anywhere in the workbook. This guide compares them side by side, solves the same task with each, and shows how they work best together.

LET vs LAMBDA at a glance#

LET is about naming inside a formula; LAMBDA is about building a function.

Both functions show #NAME? in versions that don't support them, so check what your colleagues use before you rely on either.

What LET does#

LET lets you give a name to a value or a calculation, then use that name in the rest of the formula. You write pairs of names and values, and the last argument is the result:

=LET(total,SUM(B2:B6),IF(total>10000,total*0.05,total*0.03))

Here total is calculated once, then used three times. Without LET, you'd write SUM(B2:B6) three times, which is longer and harder to check. The name exists only inside this formula: another cell can't use total.

LET helps most with long formulas: a FILTER that you also want to count, a lookup you test and then return, or a rate you use in several places. The full guide to the LET function covers naming rules and errors.

What LAMBDA does#

LAMBDA defines a function. You list its inputs, called parameters, and then the calculation that uses them:

=LAMBDA(sales,IF(SUM(sales)>10000,SUM(sales)*0.05,SUM(sales)*0.03))

On its own in a cell, this shows #CALC!, because the function has no inputs yet. To make it useful, save it:

1. Go to Formulas > Name Manager > New (or Formulas > Define Name).
2. Name it COMMISSION.
3. Paste the LAMBDA formula into Refers to and click OK.

Now any cell in the workbook can use =COMMISSION(B2:B6), or =COMMISSION(C2:C9) for another rep. If the commission rule changes, you edit the name once and every formula updates. The full guide to the LAMBDA function shows how to test and save one step by step.

Because LAMBDAs are saved as names, they follow the same rules as named ranges in Excel: a name must start with a letter, can't contain spaces and can't look like a cell address.

The same calculation solved both ways#

Here are five weeks of sales in B2:B6: 2,400, 3,100, 1,800, 2,900 and 1,200. The rule is 5% commission if total sales are over 10,000, otherwise 3%.

Plain formula#

=IF(SUM(B2:B6)>10000,SUM(B2:B6)*0.05,SUM(B2:B6)*0.03)

The total is 11,400, which is over 10,000, so the result is 11,400 × 5% = 570. It works, but SUM(B2:B6) appears three times. If you change the range, you must change it three times.

With LET#

=LET(total,SUM(B2:B6),IF(total>10000,total*0.05,total*0.03))

Same result, 570. The range appears once. Use this when the formula lives in one place.

With LAMBDA (and LET inside it)#

Save this in Name Manager as COMMISSION:

=LAMBDA(sales,LET(total,SUM(sales),IF(total>10000,total*0.05,total*0.03)))

Then call it anywhere:

=COMMISSION(B2:B6)

Again 570. The LAMBDA defines the input (sales), and LET inside it names the step (total). This combination is the usual pattern for custom functions: LAMBDA for the outside, LET for the inside.

To test a LAMBDA before saving it, add the inputs in brackets straight after it: =LAMBDA(sales,SUM(sales)*0.05)(B2:B6) returns 11,400 × 5% = 570.

When to use LET#

Use LET when a single formula repeats work or is hard to read. It's the better choice when:

• A calculation appears more than once in the same formula, like the SUM above.
• You want readable steps, such as rate, sales and bonus instead of cell addresses.
• The logic is only needed in one place, so a saved function would be overkill.
• Speed matters, because LET calculates a repeated part once instead of several times.
• You share the file with Excel 2021 users, who have LET but not LAMBDA.

LET is also the easier one to learn, because nothing is saved outside the cell. Anyone who opens the cell sees the whole formula.

When to use LAMBDA#

Use LAMBDA when the same logic is used in many cells, sheets or reports. It's the better choice when:

• You copy one complex formula into many places and fear they'll drift apart.
• A rule may change, such as a commission rate or a fiscal-year start, and you want one place to update it.
• You want a clear name, like =TENURE(B2) or =PCTCHANGE(B2,C2), that colleagues understand.
• You need helper functions such as MAP, BYROW or REDUCE, which take a LAMBDA to apply to each value.

The cost is visibility. A saved LAMBDA lives in Name Manager, not in the cell, so a colleague who doesn't know to look there may not understand what =COMMISSION(B2:B6) does. Add a comment in the Name Manager's Comment box.

Common mistakes#

These are the slips people make when they switch between the two:

1. Typing a LAMBDA into a cell and expecting a result. It shows #CALC! until you give it inputs or call it by name.
2. Expecting LET names to work in other cells. A LET name only exists inside its own formula. For a name the whole workbook can use, define a name in Name Manager. The guide on named ranges vs cell references explains when that helps.
3. Forgetting the final calculation in LET. =LET(total,SUM(B2:B6)) has a name but no result, so Excel rejects it. The last argument must be the calculation.
4. Using a name that looks like a cell. Names like tax1 or AB12 clash with cell addresses. Use tax_rate instead.
5. Sharing with older versions. A file full of LAMBDA names shows #NAME? in Excel 2021 and earlier.

LET and LAMBDA both arrived with the dynamic array era of Excel. The overview of dynamic arrays in Excel explains how spilling formulas fit in.

Our recommendation#

Start with LET. It makes long formulas shorter and easier to check, it works in Excel 2021 as well as Microsoft 365, and nothing is hidden in Name Manager. Reach for LAMBDA when you notice yourself copying the same complex formula into many places, or when a rule should live in one spot so it can change safely. When you do write a LAMBDA, use LET inside it to name the steps.

In short: LET to tidy one formula, LAMBDA to reuse logic, and both together for custom functions.

Practise it#

Both functions build on the newer dynamic-array way of writing formulas. Practise modern Excel functions in a live spreadsheet: you type FILTER, SORT and UNIQUE formulas and the page checks them instantly. Then try the exercise on this page and count how many times the repeated SUM could be replaced by a single LET name.