9. Dynamic Arrays

9.18 Named LAMBDA Functions in Name Manager, with Optional Arguments (ISOMITTED)

By Ravindra Bagale · Last updated: 6 October 2026

Example first. In lesson 9.7 you met LAMBDA briefly. Now Ruhi wants a real, reusable function for the team: LATEFLAG should say "Late" when a delivery took more than 15 minutes. But the Kolhapur store uses a 12-minute promise. So the limit must be optional: if you give it, use it; if you leave it out, use 15. We will build LATEFLAG step by step, save it in Name Manager and call it like a built-in function: =LATEFLAG(H2) or =LATEFLAG(H2,12).

First look at the data (before → after):

Expected result — J2 and K2 spill

Name: LATEFLAG     Scope: Workbook
Refers to: =LAMBDA(mins,[limit],IF(mins>IF(ISOMITTED(limit),15,limit),"Late","On time"))
J2: =LATEFLAG(H2:H11)      K2: =LATEFLAG(H2:H11,12)

How a LAMBDA is built

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

• The parameters are names for the inputs (mins, limit). Up to 253. No full stop (period) in a name, and do not use names that look like cell addresses (A1, H2).
• The calculation is the last argument. It uses the parameter names like variables, the same way LET does (lesson 9.6).
• A LAMBDA typed in a cell without values after it, like =LAMBDA(x,x*2), shows #CALC!. Either call it straight away, =LAMBDA(x,x*2)(5) gives 10, or save it with a name.

The Name Manager fields (Windows)

On a Mac use Formulas › Define Name.

How the optional argument works, piece by piece

1. [limit] in square brackets tells Excel that limit may be left out. Optional parameters must come after the required ones.
2. ISOMITTED(limit) returns TRUE when the user did not give a limit.
3. IF(ISOMITTED(limit),15,limit) therefore means "15 if missing, otherwise what the user typed".
4. Microsoft's own ISOMITTED example: =LAMBDA(x,y,IF(ISOMITTED(y),"Missing second argument",x+y))(1,) returns "Missing second argument".

Two more useful functions with optional arguments

The 18% default in WITHGST is only a sample rate for practice. Check the correct GST rate for your item with your accountant; this course does not give tax advice.

Editing and testing

• To change a function, open Name Manager, select it and click Edit…. Every cell that uses it updates.
• Keep a sheet called Tests with a few calls and the answers you expect (for example =LATEFLAG(16) should be Late, =LATEFLAG(15) should be On time). After every edit, glance at that sheet.
• A named LAMBDA lives in the workbook. To use it in another file, copy a sheet that uses it into the new workbook (the names come along) or keep a template workbook.

Common problems

How these results were checked: LAMBDA helper functions do not run in LibreOffice, so each expected value was computed in Python and the totals were cross-checked in LibreOffice with classic formulas (SUMIF, running SUM, SUMPRODUCT). Microsoft reference: LAMBDA function, ISOMITTED function. Optional parameters: Microsoft 365 Insider blog.

Practice task (try it before you open the answer):

1. Create a named function DELIVERYSLA(mins,[promise]) that returns "Within SLA" when mins ≤ promise, and "Missed" otherwise. The default promise is 15.
2. Use it on H2:H11 with the default and with a promise of 10.
3. How many orders miss a 10-minute promise?