formulastreak

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.

Saturday bakery stall · edit D3 and watch it recalculate
Click a cell (or use the arrow keys) to insert its address · Enter shows the resultD3 = $89.60
ABCD
1ItemSoldPriceTotal
2Sourdough loaf14$6.50
3Croissant32$2.80$89.60
4Cinnamon bun21$3.20
5Baguette18$3.00
6Rye loaf9$7.00

MAP over a range

MAP(range1, range2, LAMBDA(a, b, …)) runs the function on each pair of cells and spills the results.

Saturday bakery stall · edit D2 and watch it recalculate
Click a cell (or use the arrow keys) to insert its address · Enter shows the resultD2 = $91.00
ABCD
1ItemSoldPriceTotal
2Sourdough loaf14$6.50$91.00
3Croissant32$2.80$89.60
4Cinnamon bun21$3.20$67.20
5Baguette18$3.00$54.00
6Rye loaf9$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.

Q3 sales team · edit G2 and watch it recalculate
Click a cell (or use the arrow keys) to insert its address · Enter shows the resultG2 = $13,100
ABCDEFG
1RepRegionJulAugSepTargetBest month
2Amara OseiNorth$12,400$13,100$11,800$36,000$13,100
3Ben FischerSouth$9,800$10,250$12,100$30,000$12,100
4Lina ChenEast$15,200$14,800$16,900$45,000$16,900
5Diego AlvarezWest$8,700$9,100$8,400$28,000$9,100
6Eva NovakNorth$11,200$12,600$13,900$36,000$13,900
7Farid HaddadSouth$13,300$12,900$14,100$39,000$14,100
8Grace KimEast$10,100$9,600$10,800$33,000$10,800
9Hugo MartinWest$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).

Saturday bakery stall · edit D3 and watch it recalculate
Click a cell (or use the arrow keys) to insert its address · Enter shows the resultD3 = $107.52
ABCD
1ItemSoldPriceTotal
2Sourdough loaf14$6.50
3Croissant32$2.80$107.52
4Cinnamon bun21$3.20
5Baguette18$3.00
6Rye loaf9$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
Select D3 and type = to start its formula (in the cell or the formula bar)
ABCD
1ItemSoldPriceTotal
2Sourdough loaf14$6.50
3Croissant32$2.80
4Cinnamon bun21$3.20
5Baguette18$3.00
6Rye loaf9$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.

Practise it now