Skip to content
Tutorials Center
My learning

Topics

Lesson 49 of 93 ยท Dynamic Arrays

LET, LAMBDA and Reusable Formulas

Name values with LET, build your own functions with LAMBDA, and process arrays with MAP, REDUCE, SCAN, BYROW, BYCOL and MAKEARRAY, with runnable stand-ins.

Long formulas have two problems: they repeat themselves and they are hard to read. A formula that needs the same VLOOKUP three times is slow to calculate and painful to edit. And once a calculation becomes useful, you want to reuse it without copying text around.

LET fixes the first problem by giving names to intermediate results. LAMBDA fixes the second by letting you write your own functions in plain formula syntax, with no VBA. A group of helper functions (MAP, REDUCE, SCAN, BYROW, BYCOL, MAKEARRAY) then applies a LAMBDA to arrays. Excel cannot run on this site, so the Excel formulas are read-only. The JavaScript stand-ins use real closures, which is genuinely how these functions behave.

LET is available in Microsoft 365 and Excel 2021 and later. LAMBDA and the helper functions (MAP, REDUCE, SCAN, BYROW, BYCOL, MAKEARRAY) are available in Microsoft 365 and Excel for the web, and were added to Excel 2024 as well. Excel 2019 and older have none of them. Check this before sharing a workbook.

Name it, reuse it

LET Give a value or calculation a name.
LAMBDA Write your own function.
Named LAMBDA Save it in Name Manager and call it anywhere.
1 / 2

LET: variables inside a formula

LET(name1, value1, [name2, value2], ..., calculation) defines names and then evaluates the last argument, which uses them. Names and values come in pairs, and each value can use the names defined before it.

Without LET, a price after a discount and tax might look like this:

TEXT
=IF(B2*(1-C2) > 100, B2*(1-C2)*1.2, B2*(1-C2)*1.2 + 5)

B2*(1-C2) is calculated three times. With LET:

TEXT
=LET(
    net,   B2*(1-C2),
    gross, net*1.2,
    IF(net > 100, gross, gross + 5)
)

Three benefits: you read it as a story, you fix a rate in one place only, and Excel calculates net once. The gain is large when a name holds something expensive such as a FILTER or a lookup over a big range.

Rules to remember: a name must start with a letter, cannot look like a cell reference (A1, R2C3) and cannot contain spaces. A name exists only inside its own LET. The last argument is always the result, never a name.

You can store a whole array under a name, which is what makes LET shine with dynamic arrays:

TEXT
=LET(
    data,   A2:C100,
    sales,  CHOOSECOLS(data, 3),
    big,    FILTER(data, sales > 1000, "none"),
    SORT(big, 3, -1)
)

LAMBDA: your own function

LAMBDA([parameter1, ...], calculation) creates a function. The last argument is the calculation and the earlier ones are parameter names. Typed in a cell and followed by arguments in a second set of parentheses, it runs immediately. This is only useful for testing:

TEXT
=LAMBDA(x, y, x * y)(6, 7)       42
=LAMBDA(r, PI() * r^2)(10)       314.159...

A LAMBDA becomes useful when you give it a name. Open Formulas > Name Manager > New, type a name such as GROSS, and in "Refers to" enter:

TEXT
=LAMBDA(net, [rate], net * (1 + IF(ISOMITTED(rate), 0.2, rate)))

Now any cell in that workbook can use =GROSS(B2) or =GROSS(B2, 0.07). Parameters in square brackets are optional, and ISOMITTED tells you whether the caller left one out. Named lambdas are saved in the workbook, so copy the sheet or share the file and the function goes along. Recursion works too: a named LAMBDA may call itself by name, which is powerful but easy to get wrong. Test with small inputs.

A forgotten argument is a common mistake. A LAMBDA called with the wrong number of arguments returns #VALUE!. If you see #CALC! it usually means the formula returned an empty array.

The helper functions

These functions take a LAMBDA as an argument. Think of LAMBDA(x, ...) as "what to do with one thing".

Function What it does Result
MAP(array, LAMBDA(x, ...)) Applies the function to each element Same shape as the input
REDUCE(start, array, LAMBDA(acc, x, ...)) Folds the array into one running total A single value
SCAN(start, array, LAMBDA(acc, x, ...)) Like REDUCE, but keeps every step Same shape as the input
BYROW(array, LAMBDA(row, ...)) Calls the function for each row One value per row
BYCOL(array, LAMBDA(col, ...)) Calls the function for each column One value per column
MAKEARRAY(rows, cols, LAMBDA(r, c, ...)) Builds an array from row and column numbers rows by cols

Some examples, all read-only:

TEXT
=MAP(A2:A10, LAMBDA(x, x * 1.2))                  each price plus 20%
=MAP(A2:A10, B2:B10, LAMBDA(a, b, a * b))         two arrays at once
=REDUCE(0, B2:B10, LAMBDA(acc, x, acc + x^2))     sum of squares
=SCAN(0, B2:B10, LAMBDA(acc, x, acc + x))         running total
=BYROW(B2:D10, LAMBDA(r, MAX(r)))                 the best of each row
=BYCOL(B2:D10, LAMBDA(c, AVERAGE(c)))             an average per column
=MAKEARRAY(5, 5, LAMBDA(r, c, IF(r = c, 1, 0)))   a 5 by 5 identity matrix

BYROW and BYCOL solve a problem that plain functions cannot: =MAX(B2:D10) gives one number, not one per row. One restriction applies. The function inside BYROW, BYCOL and REDUCE must return a single value per call, not an array.

An old trick, =SUMPRODUCT(...) per row with helper columns (see SUMPRODUCT), becomes a single spilling formula. SCAN replaces the "running total" column where each cell adds the cell above. For ordinary totals, SUM and SUMIFS stay simpler and faster, so use these helpers when no built-in function fits.

Stand-in 1: LET and named LAMBDA with closures

JavaScript closures work like LET: each name is visible to the later ones. The code below is our model of the behavior, not Excel's engine. It chains the LET pairs as sequential bindings.

JS LET and a named LAMBDA as JavaScript closures

Press Run (or Ctrl + Enter) to see the console output here.

No output.

Stand-in 2: MAP, REDUCE, SCAN, BYROW, BYCOL and MAKEARRAY

Each function is only a few lines of JavaScript, which makes it clear what the Excel version does. Arrays are arrays of rows, like the grids in SEQUENCE, RANDARRAY and Array Arithmetic.

JS The LAMBDA helper functions in JavaScript

Press Run (or Ctrl + Enter) to see the console output here.

No output.

Choosing the right tool

Start with the simplest tool and move down this list only when needed:

  1. A built-in function (SUM, SUMIFS, XLOOKUP).
  2. LET, when a formula repeats a calculation or becomes hard to read.
  3. MAP, BYROW, BYCOL, SCAN or REDUCE, when you need a per-item result that no built-in function gives.
  4. A named LAMBDA, when the same logic appears in several places.
  5. VBA or Power Query, when the task is a procedure rather than a calculation.

Keep named lambdas documented: Name Manager has a Comment field, so write what each parameter means there. Teammates on older Excel will see #NAME?, so mention the version requirement.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. What is the last argument of LET?

    LET takes name and value pairs, followed by one final calculation. That final expression is what the formula returns.
  2. 2. Why can LET make a workbook calculate faster?

    Excel evaluates a LET value once and reuses it, which matters for FILTER results and lookups over large ranges.
  3. 3. Which function returns running totals, keeping the result of every step?

    SCAN is like REDUCE but returns the accumulator after each element. REDUCE returns only the final value.
  4. 4. How do you turn =LAMBDA(r, PI()*r^2) into a reusable function?

    A LAMBDA saved in Name Manager becomes a workbook-wide function, and no macro is involved.
  5. 5. You need the maximum of every row in B2:D10 as a spilled column. Which formula fits?

    BYROW calls the function once per row and returns one value per row. BYCOL would give one value per column.

Summary

  • LET names values and intermediate results inside one formula, which improves readability and avoids repeated work.
  • LAMBDA creates functions; saved in Name Manager they become reusable workbook functions, with optional parameters and recursion.
  • MAP transforms each element, REDUCE folds an array to one value, SCAN keeps running results.
  • BYROW and BYCOL give one result per row or column, and MAKEARRAY builds an array from row and column numbers.
  • Prefer built-in functions first and check version support before you share a workbook.

Next: Creating Your First Chart.