How to Use SCAN Function in Google Sheets

This guide will explain how to use the SCAN function in Google Sheets.

When we want to scan an array and apply a LAMBDA function to each value row by row, we can use the SCAN function in Google Sheets.

The rules for using the SCAN function in Google Sheets are the following:

• The given LAMBDA function should have exactly 2 name arguments. Otherwise, the function will return a #N/A error. 
• The 2 name arguments correspond to the accumulator and current_value in order. Thus, name1 resolves to the accumulator value, and name2 resolves to the current_value in the given array.
• The LAMBDA function must use the 2 name arguments in the formula_expression argument. 
• The current_value in the given array is found row by row while we apply the LAMBDA function. 

Google Sheets released the LAMBDA function alongside a few Lambda helper functions (LHFs), which can be used to create more complex LAMBDA formulas.

Some Lambda helper functions released are the MAP function, REDUCE function, BYCOL function, BYROW function, SCAN function, and MAKEARRAY function.  

We discussed the REDUCE function, which reduces an array and returns an accumulated result. The REDUCE function will only show the result of the LAMBDA application. 

Now we will tackle the SCAN function, which scans an array and returns an output array of the intermediate values and the accumulated result in the last cell. This time the function shows the process and result of the LAMBDA application.

In this guide, we will provide a step-by-step tutorial on how to use the SCAN function in Google Sheets. Additionally, we will explore the syntax and a real example of using the function.

Great! Let’s dive right in.

The Anatomy of the SCAN Function

The syntax or the way we write the SCAN function is as follows:

=SCAN(initial_value,array_or_range,LAMBDA)

• = the equal sign is how we begin any function in Google Sheets.
• SCAN() is our SCAN function. This function will scan an array and apply a LAMBDA function to each value row by row. The output is an array of intermediate values calculated at each step.
• initial_value is a required argument. This refers to the initial accumulator value. 
• array_or_range is another required argument. This is the array or range we want to scan. 
• LAMBDA is also a required argument. This refers to the LAMBDA formula we want to apply to each value in the array_or_range. 

The Anatomy of the LAMBDA Function

The syntax or the way we write the LAMBDA function when using it with the SCAN function is as follows:

=LAMBDA(name1,name2,formula_expression)

• = the equal sign is how we activate any function in Google Sheets.
• LAMBDA() is our LAMBDA function. This function is used to create and return a custom function with a set of names and a formula that uses them.
• name1 is a required argument. This refers to the name we use for the accumulator value.
• name2 is another required argument. This refers to the current value in the input array. 
• formula_expression is a required argument. This refers to the formula we want to calculate or execute. We need to use the names we inputted in the name argument. 

Note: There is a different syntax when using the LAMBDA function as a standalone. Please refer to this guide to learn about the complete anatomy of the LAMBDA function.

Preparing the LAMBDA Function

Before we can use the SCAN function, we must prepare our LAMBDA function. Let’s say we want to calculate the running total revenue for the year 2022. 

The data set would look like this:

Our LAMBDA function would be:

=LAMBDA(RunningTotal,CurrentValue,RunningTotal+CurrentValue)

The LAMBDA must have 2 name arguments. In the formula, we used RunningTotal as our name1 argument. The name1 argument must resolve to the current value in the accumulator. 

We used the name CurrentValue as our name2 argument in the formula. The name2 argument must resolve to the first or current value in the given range. In this example, we are referring to the total revenue of January 2022. 

The last part of our LAMBDA function is the formula_expression argument. In this instance, we will add the running total value to each monthly total revenue.  

Finally, we can use this LAMBDA function inside the SCAN function to complete our task.

A Real Example of Using SCAN Function in Google Sheets

Let’s say we have the monthly revenue sales for our company for the year 2022. Our initial data set would look like this:

The spreadsheet above shows the month and the total revenue for each month. 

In this example, we want to calculate the running total revenue for the year 2022. Thus, we can utilize the SCAN function to get an array of the intermediate revenue obtained each month.

Moreover, we want to do this specific task for the next years. To be able to reuse this specific formula, we will use the LAMBDA function within the SCAN function.

Our LAMBDA function would be:

=LAMBDA(RunningTotal,CurrentValue,RunningTotal+CurrentValue)

Next, we can simply input our LAMBDA function inside the SCAN function.

Our final formula would be:

=SCAN(0,B2:B13,LAMBDA(RunningTotal,CurrentValue,RunningTotal+CurrentValue))

The first part of the SCAN function is our initial_value argument. In this formula, we will have 0 as our accumulator since we only want to focus on the year 2022. 

Next, we selected the range B2:B13 which has the total revenue for each month in 2022, to serve as our array_or_range argument. Lastly, we simply input our custom LAMBDA function.

Essentially, the SCAN function will scan the first value of the given array or range and add that value to the initial accumulator value of 0. Then, it will update the accumulator value to the intermediate value obtained in the previous row. 

Our final data set would look like this:

You can make your own copy of the spreadsheet above using the link below. 

Amazing! Now we can dive into the steps of using the SCAN function in Google Sheets.

How to Use SCAN Function in Google Sheets

1. First, we will create a new column in the table to display the result. Then, we will start our formula by typing an equal sign and the function name. Our starting formula would be “=SCAN(“.

2. We will input our 0 as our initial_value. So our formula would become “=SCAN(0)”.

3. Next, we will select the range containing the values we want to scan. In this case, our formula would become “=SCAN(0,B2:B13”.

4. Then, we can input our LAMBDA function inside our SCAN function. This will make our final formula “=SCAN(0,B2:B13,LAMBDA(RunningTotal,CurrentValue,RunningTotal+CurrentValue))”.

5. We will press the Enter key to return the result.

And tada! We have successfully used the SCAN function in Google Sheets.

You can apply this guide whenever you need to scan an array using a LAMBDA formula. Check out the various other Google Sheets formulas available to create great worksheets that work for you.

FAQs:

1. What is the difference between the SCAN function and the REDUCE function?

The SCAN and REDUCE functions are both Lambda helper functions. Both functions also apply a LAMBDA to each value in the array.

The main difference between the two is the results they return. The REDUCE function reduces an array to an accumulated value and returns the total value in the accumulator.

However, the SCAN function returns an array with each intermediate value in the accumulator. 

That’s pretty much it! Make sure to subscribe to our newsletter to be the first to know about the latest guides and tutorials from us.