LET & LAMBDA

As formulas grow, they repeat themselves. The same SUM(B2:D2) appears three times, or a financial-year calculation is copied into twenty workbooks. LET gives names to the parts of a formula so you calculate each part once and can read the result. LAMBDA goes further: it lets you create your own functions, such as =FYLABEL(A2), using ordinary formulas and no programming language.

In this lesson you will rewrite long formulas with LET, build custom functions with LAMBDA, save them in the Name Manager, and apply them row by row with BYROW.

LET: name the parts of a formula

=LET(name1, value1, [name2, value2, ...], calculation)

Arguments come in pairs: a name, then its value. The last argument is the calculation that uses those names. LET works in Microsoft 365, Excel 2021 and later, and Google Sheets.

Kabir earns a quarterly bonus: 5% of his three months of sales if they total ₹1,00,000 or more, otherwise 2%. His sales are in B2:D2 (42000, 38500 and 27600). Without LET, the total appears three times:

=IF(SUM(B2:D2)>=100000, SUM(B2:D2)*5%, SUM(B2:D2)*2%)

With LET, it is calculated once and has a meaningful name:

=LET(total, SUM(B2:D2), IF(total>=100000, total*5%, total*2%))

5405

The total is 108100, which meets the target, so the bonus is 5%. If the rule changes to three slabs, you edit one readable formula instead of hunting for every copy of SUM(B2:D2).

Names follow the Name Manager rules: they start with a letter, contain no spaces, and must not look like a cell address. gst18 is not allowed (it is a cell in column GST), but gst_rate is fine. Names exist only inside that one LET formula.

A longer LET: the financial year label

In Date & Time Functions you built the financial-year label in two cells. LET does it in one, with each step named:

=LET(d, A2,
     start, IF(MONTH(d)>=4, YEAR(d), YEAR(d)-1),
     "FY " & start & "-" & RIGHT(start+1, 2))

FY 2026-27

For 30-Oct-2026 in A2, start is 2026. You can press Alt+Enter inside the formula bar to put each pair on its own line, as shown here; Excel ignores the line breaks.

LAMBDA: write your own function

=LAMBDA([parameter1, parameter2, ...], calculation)

A LAMBDA lists parameters (the inputs your function will receive) and then the calculation that uses them. LAMBDA needs Microsoft 365 or Excel 2024; Google Sheets also has it.

Typing a LAMBDA into a cell on its own gives #CALC!, because the function has not been given any inputs. To test it, add the inputs in a second pair of brackets right after it:

=LAMBDA(amount, rate, ROUND(amount*(1+rate), 2))(649, 5%)

681.45

The function received 649 as amount and 5% as rate. The 5% here is just an example rate.

Saving a LAMBDA in the Name Manager

A tested LAMBDA becomes a real function when you give it a name:

1. Go to Formulas > Name Manager > New (on a Mac, Formulas > Define Name).
2. In Name, type FYLABEL.
3. In Comment, describe the input, for example "Returns the Indian financial year of a date". This text appears as a tooltip when you use the function.
4. In Refers to, paste the formula without the test brackets:

=LAMBDA(d, LET(start, IF(MONTH(d)>=4, YEAR(d), YEAR(d)-1), "FY " & start & "-" & RIGHT(start+1, 2)))

5. Click OK and Close.

Now any cell in the workbook can call it like a built-in function, and AutoComplete suggests it:

Two more useful functions to save the same way:

The INDIAN function wraps the custom format from Formatting & Indian Number Format, so nobody has to remember it. Named functions belong to the workbook. To reuse them elsewhere, copy a sheet that uses them into the other workbook, or keep them in a template.

BYROW: apply a LAMBDA to every row

Some functions take a LAMBDA as an argument and run it many times. BYROW runs it once for each row of a range and spills one result per row (Microsoft 365 and Excel 2024). Here are marks out of 100:

How many subjects did each student pass (33 or more)? Type one formula in E2:

=BYROW(B2:D5, LAMBDA(row, SUM(--(row>=33))))

3
1
2
3

For each row, row>=33 gives three TRUE or FALSE values, the double minus -- turns them into 1s and 0s, and SUM counts the 1s. Its relatives are BYCOL (one result per column), MAP (one result per cell), REDUCE and SCAN (a running calculation).

Common mistakes

• Entering a LAMBDA without calling it. The cell shows #CALC!. Test it with (inputs) after it, or save it in the Name Manager.
• Forgetting that the last LET argument is the calculation. =LET(total, SUM(B2:D2)) has no calculation and returns an error.
• Names that look like cell addresses. tax1 and gst18 are cell addresses; use tax_rate instead.
• Sharing with older versions. A file using LAMBDA shows errors in Excel 2021 and earlier. Check what your colleagues use.

Key points

• LET names values inside a formula so each part is calculated once and the formula is easier to read.
• The last argument of LET and of LAMBDA is always the calculation.
• LAMBDA defines parameters and a calculation; save it in the Name Manager to create a custom function.
• BYROW, BYCOL, MAP, REDUCE and SCAN apply a LAMBDA across ranges.
• LET needs Excel 2021 or later; LAMBDA and its helpers need Microsoft 365 or Excel 2024.

=LAMBDA(p, IFS(p>=90,"A", p>=75,"B", p>=60,"C", p>=33,"D", TRUE,"Needs support"))

=BYROW(B2:D5, LAMBDA(row, ROUND(AVERAGE(row), 1)))
=MAP(E2#, LAMBDA(p, GRADE(p)))

72.7   C
39.3   D
55     D
61.7   C