Harnessing the Power of the SCAN and MAP Functions

Overview

Dynamic Array Functions (more commonly known as DAFs) are completely changing how people model out and build Excel files.  The biggest reason is because of the ability to spill across multiple cells and work with arrays of cells rather than just one at a time.  And the timing is perfect…while they may seem tricky to learn for newbies, GenAI tools like ChatGPT, Copilot, and Gemini make them so much easier to learn and build.

In this article, we’ll walk through two specific DAFs that are especially powerful: SCAN and MAP.  We’ll cover why they are worth using, how they work, and examples of each.  Make sure to grab a copy of the example workbook that accompanies this article using the box below!  And if you want examples of all of the DAFs along with tips and gotchas, check out this comprehensive template.

Why use SCAN and MAP

There are a variety of reasons why both of these are helpful to use in your formula-heavy Excel files:

1. Simplicity – instead of requiring helper columns and nested IF statements, these can house complex logic in one formula
2. Spill-ability – they can naturally spill results across multiple cells when the calculation results in an array of values
3. Efficiency – they can often calculate more quickly using one spilled formula instead of multiple separate formulas
4. Consistency – they help you reduce errors by housing the logic in one formula instead of multiple formulas

How to use SCAN and MAP

With both SCAN and MAP, a LAMBDA calculation is required.  This can be a generic, “anonymous” LAMBDA function as you’ll see in the examples below, or it can be a named LAMBDA function defined within your workbook.  Check out this detailed article covering the LAMBDA function to learn more.

SCAN

The SCAN function lets you apply a custom calculation to each element in an array/range and returns an array that contains the intermediate values created during the scan. It is often used to generate running totals, running counts, and other calculations to show intermediate/aggregated results.

Take a look at the example in the image below how the results in column C accumulate with a simple running total:

There are three arguments to the SCAN function:

1. The initial value – which is often zero as in this case
2. The referenced array/range – in this case, it’s using a shortcut for the array that starts with B2 (the hashtag references the bottom of an array)
3. The calculation to apply – in this case, it’s using a generic LAMBDA function, but it could also be a named LAMBDA custom function if applicable

The LAMBDA calculation itself needs to have three arguments:

1. The accumulator – which in this case is the “a” reference
2. The value for each array element – which in this case is the “b” reference
3. The calculation that references them both – which is very simple in this case with “a+b”

The calculation can be much more complex than just basic math if desired.  Note that if you just want the final result after applying all the intermediate calculations, you can used the REDUCE function.

MAP

The MAP lets you apply a custom calculation to each value in a supplied array, and the result from the MAP function is an array of results with the same size as the original array.

Take a look at the example in the image below how the results in column C are based on the ranges provided from column A and column B:

There are at least two arguments required with the MAP function:

1. The array to be mapped (required) – which is the range in column A in this case. Note that additional referenced arrays can be supplied – which is the range in column B in this case.
2. The calculation to apply – in this case, it’s using a generic LAMBDA function, but it could also be a named LAMBDA custom function if applicable. Note that regardless of how many additional arrays are supplied, the LAMBDA function must be the last argument supplied.

The LAMBDA calculation itself needs to have the same number of arguments as the MAP function (at least two):

1. The referenced arrays – which in this case are the “a” and “b” references
2. The calculation that references all the arrays supplied with other arguments – in this case the FIND function is being used.

The calculation can be much more complex if desired.

Video

Summary

DAFs are changing the formula approach for Excel users.  No longer do you have to think of formulas one cell at a time – you can think of them in terms of blocks with shifting sizes based on updates to your source data.  SCAN and MAP are two great ways to take that approach and apply calculations on arrays and either accumulate them, or calculate them independently.  They’ll save you time and ensure completeness and accuracy with your calculations.  Think of it as future-proofing your Excel files.

Have you done some interesting things with the SCAN or MAP functions yet?  Let us know in the comments below!