9.7 LAMBDA (Brief)
In short: LAMBDA (Microsoft 365 / Excel 2024) lets you create your own function without VBA.
LAMBDA (Microsoft 365 / Excel 2024) lets you create your own function without VBA.
Steps in Excel – a reusable delivery-fee function
- Test the logic in a cell first:
=LAMBDA(amt, IF(amt>=499,0,IF(amt>=199,15,IF(amt>=99,25,30))))(270)→ 15. -
Formulas › Defined Names › Name Manager › New… › Name
DELIVERYFEE› Refers to:=LAMBDA(amt, IF(amt>=499,0,IF(amt>=199,15,IF(amt>=99,25,30)))) -
Use it like a built-in function:
=DELIVERYFEE(G2); with a spill:=DELIVERYFEE(tblMini[Amount]).
Helper functions MAP, REDUCE, SCAN, BYROW and BYCOL use LAMBDA inside, e.g. running total =SCAN(0, tblMini[Amount], LAMBDA(acc,x, acc+x)). (Compare with the VBA user-defined function in Module 12.13.)
Ravindra Bagale's Tip
If you write a LAMBDA directly in Name Manager and there's a mistake, it's hard to find – many students get stuck here. First test it in a cell as =LAMBDA(...)(test value), and save it in Name Manager only if the result is correct. And remember, if the file is opened in an older Excel, this function won't work.
Ravindra Bagale's Tip – मराठी
LAMBDA थेट Name Manager मध्ये लिहिला आणि चूक असली तर शोधायला अवघड जातं – बरेच students इथे अडकतात. आधी cell मध्ये =LAMBDA(...)(test value) असा test करा, बरोबर आला तरच Name Manager मध्ये save करा. आणि file दुसऱ्या जुन्या Excel वर उघडली तर हे function चालणार नाही, हे लक्षात ठेवा.
Ravindra Bagale's Tip – हिंदी
LAMBDA सीधे Name Manager में लिखा और उसमें गलती हुई तो ढूँढना मुश्किल होता है – बहुत से students यहाँ अटक जाते हैं. पहले cell में =LAMBDA(...)(test value) ऐसे test करो, सही आए तभी Name Manager में save करो. और याद रखो, file किसी पुराने Excel में खुली तो यह function नहीं चलेगा.
Full LAMBDA lessons. This was the short version. Lessons 9.18 to 9.20 below go much deeper: named LAMBDA functions in Name Manager with optional arguments (ISOMITTED), MAP, REDUCE, SCAN, BYROW, BYCOL and MAKEARRAY with real examples, recursion, and a small reusable function library.
Practice tasks (10+)
Try these on a blank sheet. Open a hint only if you are stuck. No quiz — just practice.
1. Rebuild the smallest example of lambda brief on a blank sheet for CityBasket Retail.
Hint: Use only the steps on this page. Keep numbers numeric.
2. Change one input for Meera Joshi and confirm the result updates.
Hint: If the result is stuck, check absolute vs relative references.
3. Repeat the same steps with a £ or € value for James O'Neill.
Hint: Formatting must not break SUM.
4. Write the result meaning in one plain sentence next to the cell.
Hint: Imagine explaining it to a new joiners.
5. Add a second row and fill the method down.
Hint: Double-click the fill handle if the pattern is consistent.
6. Break the example on purpose, then fix it using this lesson.
Hint: Common break: text instead of a number.
7. Apply lambda brief to a 5-row made-up CityBasket Retail table.
Hint: Keep column headers clear.
8. Check your answer with a simple total or COUNT.
Hint: A wrong total usually means a format or key mismatch.
9. Swap ₹ and $ on one column and re-check totals.
Hint: Stored values stay the same; only the display changes.
10. List two mistakes beginners make with lambda brief.
Hint: Look back at the tip box on this page.
11. Teach the steps out loud in under one minute.
Hint: Why → What → How.
12. Save the sheet as CityBasketR_practice.xlsx.
Hint: Use a clear file name for class.
Quick revision cards
Three standalone cards. Each one explains the idea with its own example. Scan the QR to open this lesson.
