Excel ISOMITTED function
The ISOMITTED function checks if the value is omitted in the LAMBDA function and returns TRUE (value is missing) or FALSE (value is not missing).
It is a logical function which can be used in combination with the IF function as part of the LAMBDA function.
Note: This function is only available in Excel for Microsoft 365.
Argument (required): The value in the LAMBDA function you want to check whether it is missing.
1. This function is used as part of the LAMBDA function;
2. When the specified value is missing, you can specify to return a friendly text string.
It returns a text string.
As shown in the screenshot below, you have applied the LAMBDA function to calculate a total price based on the certain quantity and unit and the result returns 0. Now you need to check whether a value is missing in the LAMBDA function. You can do as follows to achieve it.
Tips: In this example, to get the correct calculation, the full formula of the LAMBDA function should be as follows.
=LAMBDA(x,y,x*y)(B2,C2) --> B2*C2=2450
1. Select a blank cell (say E2 in this case), enter the following formula and press the Enter key to get the result.
=LAMBDA(x,y,IF(ISOMITTED(x),"the value is missing",x*y))(,C2)
1) In this formula, ISOMITTED(x) checks whether the x argument in the LAMBDA function is missing. If the x argument is missing, it will return a friendly string “the value is missing” you specified in the formula.
2) If you change the argument in the ISOMITTED function to y, it checks if the y argument in the LAMBDA function is missing. In this case, as the y argument is not missing, the function returns the product of x and y. Here the missing value x is considered as 0, so you will get the result as 0*C2 --> 0*50=0.
=LAMBDA(x,y,IF(ISOMITTED(y),"the value is missing",x*y))(,C2)
Excel LAMBDA function
The LAMBDA function creates custom, reusable functions and call them by a friendly name.
The Best Office Productivity Tools
- One second to switch between dozens of open documents!
- Reduce hundreds of mouse clicks for you every day, say goodbye to mouse hand.
- Increases your productivity by 50% when viewing and editing multiple documents.
- Brings Efficient Tabs to Office (include Excel), Just Like Chrome, Edge and Firefox.
Please leave your comments in English