Purpose
Return value
Syntax
=ISOMITTED(argument)- argument - The argument to test for.
How to use
The Excel ISOMITTED function is a helper function for LAMBDA functions to allow optional arguments. Inside a LAMBDA function, ISOMITTED will return TRUE when an argument has not been provided. ISOMITTED takes just one argument, argument, which should be the name of an argument defined in the parent LAMBDA function.
Step-by-step example
To illustrate how ISOMITTED works, imagine a simple LAMBDA formula that adds 10 any given value. With the value 100 in cell A1, this formula will return 110:
=LAMBDA(a,a+10)(A1) // returns 110
Next, we alter the formula to make both a and b variables:
=LAMBDA(a,b,a+b)(A1,10) // returns 110
=LAMBDA(a,b,a+b)(A1,20) // returns 120
With 100 in A1, if we supply 10 for b, the formula returns 110. If we supply 20 for b, the formula returns 120. So far so good.
Now let's say we want to make b optional, and we want b to default to 10 if not provided. To accomplish this, we can use ISOMITTED to check for b. We perform this check inside the IF function, then we run one calculation if b is provided and a another calculation if b is not provided:
IF(ISOMITTED(b),a+10,a+b) // test for b
Finally, we place the snippet above into the LAMBDA function as before. We also enclose b in square brackets [b] to follow the convention of optional arguments in Excel:
=LAMBDA(a,[b],IF(ISOMITTED(b),a+10,a+b))(A1) // returns 110
=LAMBDA(a,[b],IF(ISOMITTED(b),a+10,a+b))(A1,20) // returns 120
Now if we don't provide a value for b, the formula returns a + 10. If we do provider a value for b, the formula returns a + b.
Note: the formulas above are using the special syntax for LAMBDA functions, where argument values are provided after the function in parentheses. Read more about the LAMBDA function here.
Worksheet Example
In the worksheet shown above, we are using the LAMBDA function to check password length. The LAMBDA takes two arguments, a and b. A is the password to check, and b is the required length. B is an optional argument and defaults to 8 when not provided. The formula in D5, copied down, is:
=LAMBDA(a,[b],IF(ISOMITTED(b),LEN(a)>=8,LEN(a)>=b))(B5)
Since b is not supplied, the passwords in column B are checked for a minimum length of 8 characters. The formula returns TRUE if a password is at least 8 characters long and FALSE if not. To check for a minimum length of 10 characters, simply provide a value for b:
=LAMBDA(a,[b],IF(ISOMITTED(b),LEN(a)>=8,LEN(a)>=b))(B5,10)
In this version, b has been provided as 10. The formula returns TRUE if a password is at least 10 characters long and FALSE if not. The screen below shows how the formula works after we have created a named LAMBDA function called "PasswordCheck" with the formula above:
The formula in D5 checks for a length of 8 characters by default. The formula in F5 checks for a length of 10 characters:
=PasswordCheck(B5)
=PasswordCheck(B5,10)
Related functions
LAMBDA Function
The Excel LAMBDA function provides a way create custom functions that can be reused throughout a workbook, without VBA or macros.
LET Function
The Excel LET function lets you define named variables in a formula. There are two primary reasons you might want to do this: (1) to improve performance by eliminating redundant calculations and (2) to make more complex formulas easier to read and write....
