How to use the SCAN Function?
Table of Contents
Arguments of the SCAN Function: 2
Example 1: Concatenating characters 2
Example 3: Filling Blanks with the same item. 7
Introduction
Before we explore the SCAN function, let’s figure out what the term “array” means.
Essentially, an array is a collection of items. The items can be text or numbers and they can reside in a single row or column, or in multiple rows and columns.
The difference between an array formula and a regular formula is that an array formula processes several values instead of just one. In other words, an array formula in Excel evaluates all individual values in an array and performs multiple calculations on one or several items according to the conditions expressed in the formula.
Not only can an array formula deal with several values simultaneously, it can also return several values at a time. So, the results returned by an array formula is also an array.
The SCAN Function is one of those functions Called the Lambda Helper Functions.
The SCAN Function deals with an array of items (numbers or text).
The SCAN function goes through each value in an array, applies a LAMBDA function, and returns an array of each intermediate value.
Arguments of the SCAN Function:
=SCAN(initial_value, array, LAMBDA)
Syntax
=SCAN ([initial_value], array, lambda(accumulator, value))
The SCAN function syntax has the following arguments and parameters:
- [initial_value] ► Sets the starting value for the accumulator.
- array ► An array to be scanned.
- lambda ► A LAMBDA that is called to scan the array. The LAMBDA takes two parameters:
- accumulator: The value totaled up and returned as the final result.
- Value: The calculation applied to each element in the array.
Note: Use the initial_value argument to set the starting value for the accumulator parameter. If you are working with text, set the initial value to “”.
Let’ see some examples:
Example 1: Concatenating characters
We have some characters in column A from A1 to A10 (The Array to process)
In column B we want to concatenate the contents of the cell above to the contents of the cell to my left.
Let’s do it first with the joining operator of Excel & (Shift +7)
You can read in column C the formula used in Column B.
This function must be copied down manually.
Each function acquires its result by looking at the cell above and the cell to the left.
Now let’s do it with the SCAN function
We need to understand what the X and Y of the lambda function refer to:
You may use any other names for the X and Y .
The X is the initial value but, with each calculation the X acquires a NEW value (The intermediate calculations). So the first X is “” the next X is A, the next X is AB and so on.
The Y, refers to each individual value in the selected array (A1:A10)
Lambda is the calculation we want. We need to join Each New X to each value in the array Y
Because the SCAN is a dynamic array function (available in Microsoft 365), you create the function once and it spills to the adjacent cells.
Example 2: Running Total
We need to create a running total. There are so many methods for doing that.
I will do it first with a simple formula.
In column B, I start with the value in cell A1 (adding zero does not change anything but matches the concept of the SCAN function. Think of the zero as the initial value). Starting from cell B2 going down, each calculation adds the value in the cell above to the value from column A in the same row.
Read the formulas in column C 
Now, let’s do it with the SCAN Function
The operation of the SCAN function is similar to that of the REDUCE function, but unlike REDUCE which only returns the final result, SCAN returns all the intermediate values that lead to this same final result.
Example 3: Filling Blanks with the same item.
In this example we have some Regions in column A, Sales Rep in column B and Sales Amount in column C. When creating the setup, transactions in the same Region have one single label, which does not help when using other analytical tools (such as Pivot Tables). We need to fill the blanks with correct Region.
There are different ways to fix the problem. A simple one is by selecting the range of Regions in Column A ► Go To Special ► Blanks ► =A2 ► CTRL +Enter
But, I will be using a SCAN function to do that in a new column E. Read the function in column F.
The initial value is “” (X)
The Array is A2:A16
Then I use an IF function to evaluate each cell in the array (Y), if it has contents (not blank) return that content (Y) otherwise return the new (X)
If you intend to create a Pivot table, Insert 2 new columns to the left of Manager. You may create the function in the new column C then use the range C1:D16 for creating a Pivot Table
A better single cell function to rebuild the entire list in E1, could be:
=HSTACK(SCAN(“”,A1:A16,LAMBDA(X,Y,IF(Y=””,X,Y))),B1:C16)
Then, Create a Pivot Table from the spilled array
Conclusion
The SCAN function is a powerful and very useful function when combined with other functions. It processes an Array and returns the intermediate values of calculations.
