=LAMBDA(price,price*1.2)(B2) defines a small function with one input, price, and calls it straight away on B2: the pen's 2.5 becomes 3. On its own that is just a longer =B2*1.2. The point of LAMBDA is to give the function a name in Name Manager, so a long formula becomes =ADDVAT(B2), and to hand it to MAP, BYROW and the other functions below.
| A | B | C | |
|---|---|---|---|
| 1 | Item | Price | With VAT |
| 2 | Pen | 2.5 | 3 |
| 3 | Bag | 120 | 144 |
| 4 | Lamp | 35 | 42 |
| 5 | Mug | 8 | 9.6 |
| 6 | Desk | 150 | 180 |
LAMBDA syntax
=LAMBDA([parameter1, parameter2, ...], calculation)
- Each
parameteris a name for an input, like the names in LET. Up to 253 are allowed. - The last argument is the
calculation, which uses the parameters. - Values for the parameters go in brackets right after the closing bracket:
=LAMBDA(x,y,x*y)(3,4)returns 12.
LAMBDA, MAP, BYROW, BYCOL, SCAN, REDUCE and MAKEARRAY need Microsoft 365, Excel 2024 or Excel for the web. Excel 2021 has LET but not these. Google Sheets has LAMBDA too, and saves one under a name with Data > Named functions.
Save a LAMBDA as a custom function
A LAMBDA becomes reusable when you give it a name. Excel needs no VBA and no add-in for this:
- Go to Formulas > Name Manager and click New (or Formulas > Define Name).
- In Name, type the function name, for example
ADDVAT. - In Refers to, enter the LAMBDA without inputs:
=LAMBDA(price,price*1.2). - Click OK. Now type
=ADDVAT(B2)in any cell of the workbook.
Name: ADDVAT
Refers to: =LAMBDA(price,price*1.2)
In a cell: =ADDVAT(B2) returns 3 when B2 is 2.5
The function lives in that workbook only. Copy a sheet that uses it into another workbook and the name comes along. Change the LAMBDA once in Name Manager and every cell that calls it updates. Test a LAMBDA in a cell with inputs in brackets before you save it; a mistake is easier to see there.
MAP: apply a LAMBDA to every cell
MAP calls the LAMBDA once for each cell of a range and returns a range of the same shape. Here every price over 100 gets 10% off:
| A | B | C | |
|---|---|---|---|
| 1 | Item | Price | Price to pay |
| 2 | Pen | 2.5 | 2.5 |
| 3 | Bag | 120 | 108 |
| 4 | Lamp | 35 | 35 |
| 5 | Mug | 8 | 8 |
| 6 | Desk | 150 | 135 |
The bag (120) becomes 108 and the desk (150) becomes 135; the other prices pass through. One formula in C2 covers the whole column. MAP can also walk two ranges of the same size side by side: with quantities in D2:D6, =MAP(B2:B6,D2:D6,LAMBDA(p,q,p*q)) multiplies each price by its quantity.
BYROW: one result per row
BYROW hands the LAMBDA a whole row at a time, so the LAMBDA can use MAX, SUM or AVERAGE on it. Each student's best and average score:
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Student | Test 1 | Test 2 | Test 3 | Best | Average |
| 2 | Ann | 72 | 85 | 90 | 90 | 82.3 |
| 3 | Ben | 64 | 70 | 58 | 70 | 64 |
| 4 | Cara | 88 | 92 | 95 | 95 | 91.7 |
| 5 | Dan | 75 | 60 | 81 | 81 | 72 |
E2 returns 90, 70, 95 and 81; F2 returns 82.3, 64, 91.7 and 72. A plain =MAX(B2:D5) would give one number for the whole table; BYROW is what keeps rows apart in a single formula. BYCOL does the same per column: =BYCOL(B2:D5,LAMBDA(c,AVERAGE(c))) returns the average of each test.
SCAN and REDUCE: running totals
REDUCE walks a range and carries a value along, returning only the final result. SCAN does the same but returns every step, which makes it a one-formula running total:
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Item | Price | Running total | Total |
| 2 | Pen | 2.5 | 2.5 | 315.5 |
| 3 | Bag | 120 | 122.5 | |
| 4 | Lamp | 35 | 157.5 | |
| 5 | Mug | 8 | 165.5 | |
| 6 | Desk | 150 | 315.5 |
The first argument, 0, is the starting value. For each price, the LAMBDA receives the total so far and the price, and returns the new total. C2 runs 2.5, 122.5, 157.5, 165.5, 315.5, and D2 shows only the final 315.5. For a plain total SUM is simpler, but REDUCE can carry anything, such as a text that grows or a count that only rises on some rows.
Name a LAMBDA inside one formula with LET
A LAMBDA does not need Name Manager if only one formula uses it. Name it with LET and pass the name to MAP or BYROW:
| A | B | C | |
|---|---|---|---|
| 1 | Item | Price | Sale price |
| 2 | Pen | 2.5 | 2.25 |
| 3 | Bag | 120 | 108 |
| 4 | Lamp | 35 | 31.5 |
| 5 | Mug | 8 | 7.2 |
| 6 | Desk | 150 | 135 |
Every price gets 10% off: 2.25, 108, 31.5, 7.2 and 135. In Excel you can also call the named LAMBDA directly inside the LET, =LET(f,LAMBDA(x,x*2),f(5)), which returns 10.
Practice: a total per row with BYROW
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Student | Test 1 | Test 2 | Test 3 | Total |
| 2 | Ann | 72 | 85 | 90 | |
| 3 | Ben | 64 | 70 | 58 | |
| 4 | Cara | 88 | 92 | 95 | |
| 5 | Dan | 75 | 60 | 81 |
Your turn: In E2, return each student's total of the three tests, one number per row, with a single formula.
Common LAMBDA errors
=LAMBDA(x,x*2) #CALC! defined but never called
=LAMBDA(x,x*2)(5) 10
=LAMBDA(x,y,x*y)(3) #VALUE! two parameters, one value
=LAMBDA(x,x*2)(3,4) #VALUE! one parameter, two values
- #CALC! means a LAMBDA sits in a cell without being called. Add the inputs in brackets, or save it in Name Manager and call it by name.
- #VALUE! means the number of values does not match the number of parameters. Count them on both sides.
- #NAME? means the Excel version has no LAMBDA, or a saved name is misspelled. A parameter name follows the LET rules: no spaces, no name that looks like a cell address.
- A BYROW LAMBDA that returns several values per row gives #CALC!. Each row must produce one value; to return a row of results, use MAKEARRAY or a plain array formula instead.
Frequently Asked Questions
What is the LAMBDA function in Excel?
It turns a formula into a function with named inputs. =LAMBDA(price,price*1.2) takes one input called price and returns price times 1.2. Call it by adding the input in brackets, =LAMBDA(price,price*1.2)(B2), or save it under a name in Name Manager.
How do I create a custom function in Excel without VBA?
Open Formulas > Name Manager > New, type a name such as ADDVAT, and in Refers to enter =LAMBDA(price,price*1.2). Click OK, and =ADDVAT(B2) works in any cell of that workbook.
Why does my LAMBDA return #CALC!?
A LAMBDA typed into a cell without inputs, such as =LAMBDA(x,x*2), is a function that was never called, so Excel shows #CALC!. Add the input in brackets after it, =LAMBDA(x,x*2)(5), or save it in Name Manager.
Which Excel versions have LAMBDA?
Microsoft 365, Excel 2024 and Excel for the web, together with MAP, BYROW, BYCOL, SCAN, REDUCE and MAKEARRAY. Excel 2021 has LET but not LAMBDA.
What does BYROW do in Excel?
It runs a LAMBDA once per row of a range and returns one result per row: =BYROW(B2:D5,LAMBDA(r,MAX(r))) returns the largest value of each row, spilled down.
