Ravindra BagaleCourses & study guides Track your progress

Excel LAMBDA Function

By · Last updated:

Chala mitrano — today we learn LAMBDA. It lets you build your own reusable formula, like a tiny custom function, without VBA.

Excel 365 needed. LAMBDA needs Microsoft 365 (or Excel for the web with a 365 licence). It is not in Excel 2019 / 2016. LibreOffice does not match this fully — practice on Microsoft 365.
Fictional sample data for learning. Kirana sales (Nashik) and a small UK cafe (Leeds). Mixed ₹ and £. Names are fictional.

Purpose

Create a custom, reusable calculation. You name parameters, write the formula once, and call it like a normal function (often stored in Name Manager).

Return value

Whatever your LAMBDA body returns — a number, text, array, or error — depending on the formula you wrote inside.

Syntax

LAMBDA([parameter1, parameter2, …,] calculation)

Arguments

ArgumentRequired?Description
parameterOptional (usually used)A name for a value you will pass in (example: price, qty, rate).
calculationRequiredThe formula that uses those parameter names. This is the last argument.

Version

Microsoft 365. Not in Excel 2019 or older. Pair with Name Manager to create a lasting custom function for the workbook.

How to use — Why → What → How

Why: The same GST or tip math is pasted in twenty cells. One change means twenty edits. Interviews also ask if you know modern Excel 365 tools.

What: LAMBDA defines parameters and one calculation. You can test it in a cell, then save it as a defined name (example: NetPay).

How — numbered steps:

  1. In an empty cell, type a test LAMBDA and call it at once: =LAMBDA(x, x*1.18)(100).
  2. Check the result (118). Fix the math if needed.
  3. Select Formulas → Name Manager → New.
  4. Name it (example: AddGST). In Refers to, paste =LAMBDA(amount, amount*1.18).
  5. In any sheet type =AddGST(1000) and press Enter.

Ravindra Bagale's Tip

Friends, always test the LAMBDA in a cell with (args) at the end before saving the name. A broken Name Manager entry is harder to debug.

Ravindra Bagale's Tip

Keep parameter names short and clear: price, qty, city — not x1, x2, tmp.

Worked examples

1) Inline call — add 18% GST (₹)

Amit Deshmukh sells a mixer for ₹2,000 in Nashik. Add GST in one shot.

ItemPrice (₹)
Mixer2000
Formula: =LAMBDA(amount, amount*1.18)(2000)

Result: 2360

What it means: Parameter amount is 2000; calculation multiplies by 1.18.

2) Two parameters — line total

Priya packs 12 boxes at ₹85 each.

Formula: =LAMBDA(qty, price, qty*price)(12, 85)

Result: 1020

What it means: qty=12, price=85; product is the line total.

3) UK tip helper (£)

Emma Wright in Leeds adds a 10% tip on a £42.50 bill.

Formula: =LAMBDA(bill, tip_rate, bill*(1+tip_rate))(42.5, 0.1)

Result: 46.75

What it means: Same LAMBDA pattern works with pounds; only the numbers change.

4) Text helper — clean city label

Build a short label from city and code.

Formula: =LAMBDA(city, code, UPPER(LEFT(city,3))&"-"&code)("Nashik","NK-09")

Result: NAS-NK-09

What it means: LAMBDA can return text, not only numbers.

5) Saved name idea (after Name Manager)

After you save AddGST as =LAMBDA(amount, amount*1.18):

Formula: =AddGST(A2)

Result: (depends on A2) — for 2000 → 2360

What it means: The workbook now has a reusable custom function.

About this data. Prices and names are fictional teaching samples (Nashik kirana + Leeds cafe). Not real shop data.

Common errors and fixes

#CALC! — You entered a LAMBDA but forgot to call it with (values). Fix: Add parentheses with arguments, or save it as a name and call the name.
#VALUE! — Wrong number of arguments when calling. Fix: Match parameter count.
#NAME? — Defined name misspelled, or Excel version has no LAMBDA. Fix: Check spelling; confirm Microsoft 365.
Circular thinking — Parameter name clashes with a sheet name / cell style. Fix: Use plain parameter names (amount, rate).

Notes and tips

  • LAMBDA pairs well with MAP, REDUCE, SCAN, BYROW and BYCOL.
  • For helpers you use every day, store them in Name Manager.
  • Keep the calculation short. Long logic → break into LET inside the LAMBDA.

LET, MAP, REDUCE, SCAN, BYROW, BYCOL, MAKEARRAY

Practice tasks (10)

  1. Task 1. Write an inline LAMBDA that triples a number and call it with 15.
    Hint: LAMBDA(x, x*3)(15)
  2. Task 2. Make a two-parameter LAMBDA for qty × price; call with 8 and 120.
    Hint: last argument is the calculation.
  3. Task 3. Add 5% to a £80 bill using LAMBDA.
    Hint: bill*(1+0.05)
  4. Task 4. Build LAMBDA(city, LEFT(city,3)) and call with "Pune".
    Hint: result should be Pun
  5. Task 5. Explain in one line why =LAMBDA(x,x+1) alone shows #CALC! or looks unfinished.
    Hint: it was not called.
  6. Task 6. Create a Name Manager name Net18 for amount*1.18 and test with 500.
    Hint: Refers to starts with =LAMBDA
  7. Task 7. Write LAMBDA that returns "Big" if amount≥1000 else "Small"; test 850 and 1200.
    Hint: use IF inside.
  8. Task 8. Use your Net18 name on cell B2 instead of a typed number.
    Hint: =Net18(B2)
  9. Task 9. List two Excel versions where LAMBDA will not work.
    Hint: think 2016 / 2019.
  10. Task 10. Bonus: LAMBDA with three parameters: qty, price, discount_rate → qty*price*(1-discount_rate). Test 10, 50, 0.1.
    Hint: result 450

Quick quiz

Q1. LAMBDA needs Microsoft 365 (not Excel 2016).
Q2. The calculation argument is:
Q3. =LAMBDA(x, x*2) without a call often leads to:
Q4. To reuse a LAMBDA workbook-wide you typically use:

Answers: Q1=True, Q2=last, Q3=#CALC!, Q4=Name Manager.