Excel 365’s nifty LAMBDA feature is getting an interesting, literally optional, improvement plus ‘Helper’ functions to extend its usefulness.
LAMBDA, a powerful tool that allows users to create their own Excel 365 custom functions without VBA. The feature allows longer formulas to be written once then referenced in a worksheet to a shorter name of the user’s choosing.
LAMBDA isn’t just another Excel function, it’s an important change in how Excel works. With Lambda() Excel understand functions as a value. The new helper functions can handle input from Lambda() to do things not previously possible and also simplify coding of existing tasks.
LAMBDA now has optional parameters
Lambda() now allows for optional parameters. Simply wrap the parameter name in [ ] to let Excel know that value might not be passed into the function.
Optional parameters need a way to detect them, so welcome IsOmitted(). A new function for use inside Lamdba() to check if an optional parameter is present or not.
A simple example:
=LAMBDA(param1, [param2], IF(ISOMITTED(param2), param1, param2))
LAMBDA returns param1 if param2 is absent, otherwise it will return the value of param2.
Helper functions
Also in Excel 365 are some Lambda ‘Helper’ functions though the word ‘extension’ might be more accurate.
- MAP: generates an array created by mapping each value in the array(s) to a new value and applying the LAMBDA function to create a new value.
- REDUCE: applies a LAMBDA function to each value in an array/table and returns the total value in the accumulator.
- SCAN: Scans an array by using a LAMBDA for each value, returning an array with each mid-value.
- MAKEARRAY: creates an array of a given row and column size, using a LAMBDA function to fill each cell.
- BYROW: a selection of results by applying LAMBDA to each row.
- BYCOL: a selection of results by applying LAMBDA to each column.
Who get the Lambda() extras?
At the moment these extra Lambda() features are available to Office Insiders. Current Channel Preview for Windows v2107 build 14228.20154, Mac version 16.51 build 21071100.
Understanding the new Lambda helper functions
Lambda – strange name for a really good extra in Excel
Lambda() downsides need to be fixed
