The SCAN function is used to search an array (range) and return the calculated value based on a formula specified using the LAMBDA function. The first, optional argument, is the initial value. It is often left out. Then we specify an array (range) and at the end we state the mentioned expression by which the calculation is performed. The result of this function is not one value but a range whose dimensions correspond to a given sequence.
The REDUCE function is used to reduce a given array to an accumulated value defined by the LAMBDA function. It is somewhat reminiscent of conditional data aggregation functions but provides far more possibilities. In the example that follows, you will be able to see its application for calculating the last day of the month when sales were higher than average.
MAP is used as an auxiliary function, in combination with the LAMBDA function, it is dynamic in nature and returns all those data range values that meet the specified condition. It reminds a little of other functions for working with dynamic arrays, but instead of values, it returns those that were calculated within a given expression. The LAMBDA function is in charge of the calculation.
LAMBDA is a new Excel function that allows easy creation of formulas, for one or more parameters. It works similarly to the LET function, but offers significantly greater capabilities. This feature was announced 2 years ago and only today it was included in the Microsoft365 update, so I’m looking forward to finally sharing with you the knowledge of how to use it. Let’s get to know the LAMBDA function!
In the “recipe” Simple sales analysis, you could learn how to get to the value that is in the intersection of the row and column of the sales report. On that occasion, we used the INDEX and MATCH functions. The new XLOOKUP function allows this analysis to be performed in a much simpler way, by combining two instances of this function. Let’s see how it’s done!