SCAN Function in Google Sheets – LAMBDA Helper Function (New For 2022!)

The SCAN function in Google Sheets scans an array and applies a LAMBDA function to each value, moving row by row. The output is an array of intermediate values obtained at each step.

It’s hard to understand without seeing an example, so let’s do that.

The most straightforward SCAN formula is to create a running total calculation on a column of numbers.

The formula is:

=SCAN(0,A2:A11,LAMBDA(runningTotal,currentValue,runningTotal + currentValue))

Which produces this array output:

Scan Function Running Total Calculation

The lambda simply adds together the current running total and the new value, then returns the answer to the output array.

When all of the values from the input array have been considered, the output array is returned.

🔗 Get this example and others in the template at the bottom of this article.

Continue reading SCAN Function in Google Sheets – LAMBDA Helper Function (New For 2022!)

REDUCE Function in Google Sheets – LAMBDA Helper Function (New For 2022!)

The REDUCE function in Google Sheets turns an array input into a single value, by applying a custom LAMBDA function to each value of the input array.

It reduces an array down to a single value.

It’s best to understand through a simple example:

Cumulative Reduce Function Google Sheets

The formula in this example is:

=REDUCE(0,A2:A6,LAMBDA(total,x,total+x))

The REDUCE function takes a starting value (0 in this example), an input array, and then a custom LAMBDA function.

The formula applies the custom LAMBDA function to each value in the array. In this case, it takes the current total value and adds the next value in the array to it to make a new total. This new total is passed on to the next iteration.

So this REDUCE formula calculates the cumulative total of the 5 numbers.

(And yes, this formula also gets you there:

=SUM(A2:A6)

This REDUCE was deliberately simple to show you how it works.)

🔗 Get this example and others in the template at the bottom of this article.

Continue reading REDUCE Function in Google Sheets – LAMBDA Helper Function (New For 2022!)

MAP Function in Google Sheets – LAMBDA Helper Function (New For 2022!)

The MAP function in Google Sheets creates an array of data from an input range, where each value is “mapped” to a new value based on a custom LAMBDA function.

MAP takes an input range of data from your Sheet, then performs an operation on each value of that range, and puts the results into an output array of the same dimensions as the original array.

Let’s see a silly example to understand how the MAP function works.

We’re going to transform an array of data into thumbs up for values 5 and over, and skull and crossbones for values less than 5. Stay with me here 😉

Here’s the input array:

input Array Google Sheets

To which we apply this MAP formula:

=MAP(A2:C4, LAMBDA(value, IF(value >= 5 , "👍" , "☠️" )))

MAP passes each value to the LAMBDA, which uses a regular IF function to test if the value is greater than or equal to 5, and output a thumbs up or skull.

To give an output array of:

output Array Google Sheets

If I highlight the values, you can see how MAP evaluates each value against the test, and transforms it into a thumbs up or a skull and crossbones.

The 9 has mapped to a thumbs up, and the 2 has mapped to a skull and crossbones.

How Map Function works in Google Sheets

Ok, so this isn’t a particularly useful example, but it’s a nice illustration of how the MAP function works.

There are some more practical examples further down this post.

🔗 Get this example and others in the template at the bottom of this article.

Continue reading MAP Function in Google Sheets – LAMBDA Helper Function (New For 2022!)

XLOOKUP Function in Google Sheets (New For 2022!)

The XLOOKUP function in Google Sheets is a new lookup function in Google Sheets that is more powerful and flexible than the older lookup functions like VLOOKUP or HLOOKUP.

XLOOKUP matches a search key in a lookup range and returns the value from a result range at that same position. If XLOOKUP does not find a match, you can specify a default value. You can control the match mode, like other lookup functions, and even control the search mode. More on that below, but first let’s see a simple example.

Here’s a simple XLOOKUP formula that looks for the search key in column A and returns a value from column C:

=XLOOKUP(E2,A2:A11,C2:C11)

It looks like this in the Sheet:

XLOOKUP In Google Sheets

🔗 Get this example and others in the template at the bottom of this article.

Continue reading XLOOKUP Function in Google Sheets (New For 2022!)

LAMBDA Function in Google Sheets (New For 2022!)

They’re here! 🙂

The LAMBDA function in Google Sheets creates a custom function with placeholder inputs, that can be reused.

The use case for the LAMBDA function is to work with other lambda helper functions, like MAP, REDUCE, SCAN, MAKEARRAY, BYCOL, and BYROW.

LAMBDA functions are also the underlying technology for Named Functions, which are hugely useful.

LAMBDA functions themselves are challenging to use and not very intuitive compared to regular functions.

Lambda Function In Google Sheets

🔗 Get this example and others in the template at the bottom of this article.

Note: it may take up to 15 days from August 24th before you see lambda in your Sheets.

Continue reading LAMBDA Function in Google Sheets (New For 2022!)