The Power of Custom Functions
In Microsoft Excel, the LAMBDA function allows advanced users to create their own custom formulas from scratch. Instead of typing a massive, 5-line combination of INDEX, MATCH, and IFERROR statements over and over again, you can package that entire logic into a custom LAMBDA, name it something simple like MYSEARCH, and reuse it throughout your spreadsheet.
However, when you build a custom LAMBDA, you must define the variables it requires. If you define a LAMBDA that requires three pieces of data (e.g., Cost, TaxRate, Discount), and a user tries to run your formula but forgets to type in the Discount number, the entire function will instantly crash and display a massive #VALUE! error.
To make your custom formulas robust and user-friendly, you need a way to make certain arguments optional. You can achieve this using the ISOMITTED function.
Understanding the Syntax
The ISOMITTED function only works inside a LAMBDA formula. It performs a very simple logical check: Did the user type a value for this specific variable, or did they leave it completely blank?
=ISOMITTED(variable_name)
If the user left the argument blank, ISOMITTED outputs TRUE. If the user provided data, it outputs FALSE.
You can wrap this simple True/False check inside a standard IF statement to provide a default fallback value if the user forgets to type one.
Example 1: Making a Tax Argument Optional
Assume you are building a custom LAMBDA named CALCPRICE. It takes a base cost and multiplies it by a tax rate.
=LAMBDA(cost, tax_rate, cost * (1 + tax_rate))
If a user types =CALCPRICE(100) and hits Enter without providing a tax rate, the formula crashes because it cannot multiply by nothing.
Let’s use ISOMITTED to fix this. We want to tell Excel: “If the user forgets to type a tax rate, just assume the tax rate is zero.”
We rewrite the LAMBDA like this:
=LAMBDA(cost, [tax_rate], cost * (1 + IF(ISOMITTED(tax_rate), 0, tax_rate)))
How this works:
- The user types
=CALCPRICE(100)and leaves the second argument blank. - Excel runs the
ISOMITTED(tax_rate)check. Because it is blank, it returns TRUE. - Because the IF statement is TRUE, it injects a
0into the math. - The math becomes
100 * (1 + 0), which outputs $100. The crash is completely avoided.
(Note: It is a best practice in Excel to enclose optional variable names in square brackets, like [tax_rate], when defining the LAMBDA. This visually signals to other users that the argument is not strictly required).
Example 2: Providing Default Text Values
ISOMITTED is not just for math; it is crucial for string manipulation as well.
Assume you build a LAMBDA to generate a standardized employee ID tag. It requires a First Name, a Last Name, and a Department Code.
=LAMBDA(first, last, [dept], first & "." & last & "-" & IF(ISOMITTED(dept), "GENERAL", dept))
If the user types =IDTAG("John", "Smith", "HR"), the formula outputs John.Smith-HR.
If the user gets lazy and just types =IDTAG("John", "Smith"), the ISOMITTED function detects the missing third argument and automatically falls back to the default string, outputting John.Smith-GENERAL.
Important Limitations
You cannot use ISOMITTED on standard Excel formulas. If you try to run =ISOMITTED(A1) on an empty cell in a normal spreadsheet, it will not work. It is exclusively designed to be used inside the parameter definition of a LAMBDA function to catch missing user inputs before the core calculation executes.