Excel ISOMITTED function


The Excel ISOMITTED function is a specialized function designed to work within LAMBDA functions, checking whether an argument passed to a LAMBDA function is missing. This capability is crucial for creating flexible LAMBDA functions with optional arguments, allowing for more dynamic and adaptable formulas. ISOMITTED returns TRUE when an expected argument is not provided, making it easier to implement conditional logic based on the presence or absence of certain parameters.
  • argument: The argument to test for omission within a LAMBDA function
Return value
TRUE or FALSE, depending on whether the argument is omitted.

How to use

ISOMITTED is exclusively used within the context of LAMBDA functions to identify missing (omitted) arguments. It takes a single argument, which should be the name of an argument defined in the LAMBDA function. This function allows for sophisticated formulas where behavior changes based on whether specific arguments are provided.


Checking for a Missing Parameter and Providing Default Behavior:
					=LAMBDA(x, y, IF(ISOMITTED(y), "Missing second argument", x + y))(1,)

This LAMBDA function adds two numbers, x and y. It checks if the second argument (y) is omitted. If y is omitted, the function returns “Missing second argument”; otherwise, it proceeds with adding x and y.
ISOMITTED with Optional Arguments
Creating a LAMBDA Function with Optional Arguments:
					=LAMBDA(a, [b], IF(ISOMITTED(b), a + 10, a + b))(5)

This formula demonstrates a LAMBDA function where the second argument is optional. If the second argument (b) is omitted, the function defaults to adding 10 to the first argument (a). If b is provided, it adds a and b.
ISOMITTED in Practical Use Case
Using ISOMITTED to Apply Default or Custom Discount:
					=LAMBDA(orderQuantity, [discountRate], IF(ISOMITTED(discountRate), orderQuantity * 0.95, orderQuantity * (1 - discountRate)))("100")

In this LAMBDA function, orderQuantity represents the total number of items ordered, and discountRate is the optional argument for a custom discount rate. If discountRate is omitted, the function applies a default 5% discount to the total quantity (represented by multiplying by 0.95). If discountRate is provided, it applies the custom discount by subtracting the discount rate from 1 and then multiplying by the order quantity.

Additional Notes

  • ISOMITTED significantly enhances the functionality of LAMBDA functions by supporting optional arguments, allowing for more versatile and complex formulas.
  • This function, along with LAMBDA, was introduced in Excel for Microsoft 365, part of Excel’s ongoing expansion of advanced formula capabilities.

Related Functions

Excel LAMBDA function

The Excel LAMBDA function creates custom functions without VBA, enabling complex calculations and reusable formula components.

Excel LET function

The Excel LET function assigns names to calculation results, simplifying formulas and improving performance by reducing repetition.

Content Navigation