LAMBDA, MAP and BYROW
Updated · Works in: Excel 2024, Microsoft 365, Google Sheets · Every example checked before release
Short answer
=LAMBDA(x, x*2)(5) defines and calls a function. Name it with LET, Name Manager or Named functions, or apply it with MAP (each cell), BYROW (each row) and REDUCE (fold to one value). Needs Excel 2024, Microsoft 365 or Google Sheets (not Excel 2021).
LAMBDA(parameters, calculation) describes a function. You can call it directly, give it a name, or hand it to MAP, BYROW and REDUCE to run it over a range.
Call a LAMBDA
Put the values in brackets right after it: LAMBDA(q, p, q*p)(B3, C3). On its own, a LAMBDA shows #CALC!: it's a function, not a value.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Item | Sold | Price | Total |
| 2 | Sourdough loaf | 14 | $6.50 | |
| 3 | Croissant | 32 | $2.80 | $89.60 |
| 4 | Cinnamon bun | 21 | $3.20 | |
| 5 | Baguette | 18 | $3.00 | |
| 6 | Rye loaf | 9 | $7.00 |
MAP over a range
MAP(range1, range2, LAMBDA(a, b, …)) runs the function on each pair of cells and spills the results.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Item | Sold | Price | Total |
| 2 | Sourdough loaf | 14 | $6.50 | $91.00 |
| 3 | Croissant | 32 | $2.80 | $89.60 |
| 4 | Cinnamon bun | 21 | $3.20 | $67.20 |
| 5 | Baguette | 18 | $3.00 | $54.00 |
| 6 | Rye loaf | 9 | $7.00 | $63.00 |
BYROW and REDUCE
BYROW(range, LAMBDA(row, …)) returns one result per row. REDUCE(start, range, LAMBDA(total, value, …)) folds the range into one value, and SCAN(0, C2:C7, LAMBDA(total, v, total+v)) does the same but keeps every step: a running total.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Rep | Region | Jul | Aug | Sep | Target | Best month |
| 2 | Amara Osei | North | $12,400 | $13,100 | $11,800 | $36,000 | $13,100 |
| 3 | Ben Fischer | South | $9,800 | $10,250 | $12,100 | $30,000 | $12,100 |
| 4 | Lina Chen | East | $15,200 | $14,800 | $16,900 | $45,000 | $16,900 |
| 5 | Diego Alvarez | West | $8,700 | $9,100 | $8,400 | $28,000 | $9,100 |
| 6 | Eva Novak | North | $11,200 | $12,600 | $13,900 | $36,000 | $13,900 |
| 7 | Farid Haddad | South | $13,300 | $12,900 | $14,100 | $39,000 | $14,100 |
| 8 | Grace Kim | East | $10,100 | $9,600 | $10,800 | $33,000 | $10,800 |
| 9 | Hugo Martin | West | $14,500 | $15,800 | $15,100 | $42,000 | $15,800 |
Name it once, use it everywhere
Inside a formula, LET can hold a LAMBDA. For the whole file, save it in Excel's Name Manager or Google Sheets' Named functions and call it like =WITHTAX(B2).
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Item | Sold | Price | Total |
| 2 | Sourdough loaf | 14 | $6.50 | |
| 3 | Croissant | 32 | $2.80 | $107.52 |
| 4 | Cinnamon bun | 21 | $3.20 | |
| 5 | Baguette | 18 | $3.00 | |
| 6 | Rye loaf | 9 | $7.00 |
Check yourself
One real exercise from the “LET and LAMBDA” unit. Checked on our server; no account needed.Write a LAMBDA that multiplies quantity by price and call it on row 3, in D3.
- Use LAMBDA
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Item | Sold | Price | Total |
| 2 | Sourdough loaf | 14 | $6.50 | |
| 3 | Croissant | 32 | $2.80 | |
| 4 | Cinnamon bun | 21 | $3.20 | |
| 5 | Baguette | 18 | $3.00 | |
| 6 | Rye loaf | 9 | $7.00 |
Common mistakes
Leaving a LAMBDA uncalled (#CALC!).
Giving a LAMBDA the wrong number of values (#VALUE!).
Reaching for LAMBDA when a normal formula filled down does the job.
Questions
Which versions have LAMBDA?+
Excel 2024, Microsoft 365 and Google Sheets, along with MAP, BYROW, BYCOL, REDUCE, SCAN and MAKEARRAY. Excel 2021 has LET but not LAMBDA.
What's the difference between MAP and BYROW?+
MAP passes single cells; BYROW passes a whole row at a time.
Reading is step one. Now make it stick.
Practise it in a five-minute lesson on a live sheet, with instant feedback. No account needed for the free units.