How to Use ISOMITTED Function in Excel
The ISOMITTED function checks if a LAMBDA argument is missing. It returns TRUE if the argument was not provided. It returns FALSE if the argument was given.
What ISOMITTED Does
- Works only inside LAMBDA functions
- Checks if optional arguments are provided
- Returns TRUE for missing arguments
- Returns FALSE for provided arguments
Important: ISOMITTED only works inside LAMBDA functions. It will return #NAME? error if used alone.
Syntax
=ISOMITTED(argument)
argument is the name of a LAMBDA parameter to check.
Basic LAMBDA Example
Simple LAMBDA with Optional Argument
Create this LAMBDA function:
=LAMBDA(x,[y],IF(ISOMITTED(y),x+10,x+y))(5)
- y is optional (in square brackets [y])
- ISOMITTED(y) returns TRUE because y is missing
- Result: 15 (5+10)
Same LAMBDA with y Provided
=LAMBDA(x,[y],IF(ISOMITTED(y),x+10,x+y))(5,20)
- ISOMITTED(y) returns FALSE because y=20 was provided
- Result: 25 (5+20)
Creating Named LAMBDA Functions
Step 1: Open Name Manager
- Go to Formulas → Name Manager
- Click New
Step 2: Create Named Function
| Name | Refers to |
|---|---|
| AddBonus | =LAMBDA(price,[discount],IF(ISOMITTED(discount),price*1.1,price*(1-discount))) |
Step 3: Use Your Function
=AddBonus(100) → Result: 110 (10% bonus)
=AddBonus(100,0.2) → Result: 80 (20% discount)
Practical Examples
Example 1: Password Length Checker
=LAMBDA(password,[min_length], IF(ISOMITTED(min_length),LEN(password)>=8,LEN(password)>=min_length))(A1)
- Checks if password is 8+ characters by default
- Use second argument for custom length
Example 2: Price Calculator
=LAMBDA(base_price,[tax_rate], base_price * IF(ISOMITTED(tax_rate),1.08,1+tax_rate))(50)
- Default 8% tax if no tax rate provided
- Custom tax rate if provided
Example 3: Data Filter
=LAMBDA(data,[filter], IF(ISOMITTED(filter),AVERAGE(data),AVERAGEIF(data,">="&filter)))(A1:A10)
- Averages all data by default
- Averages data above filter value if provided
Multiple Optional Arguments
=LAMBDA(name,[age],[city],
name & IF(ISOMITTED(age),""," ("&age&")")
& IF(ISOMITTED(city),""," from "&city))("John")
- Checks multiple optional arguments
- Builds complete string step by step
When to Use ISOMITTED
- Optional LAMBDA parameters
- Default values for missing arguments
- Flexible named functions
- User-friendly functions with smart defaults
Quick Comparison
| Function | Purpose | Works With |
|---|---|---|
| ISOMITTED | Missing LAMBDA args | LAMBDA only |
| ISBLANK | Empty cells | Any cell |
| ISERROR | Error values | Any cell |
Best Practice: Always put optional arguments in square brackets [] and check them with ISOMITTED.
Summary
ISOMITTED makes LAMBDA functions flexible and user-friendly. It detects missing optional arguments so you can provide smart defaults. Use it to create reusable functions that work with or without extra parameters. Perfect for building professional, adaptable Excel tools.
