What’s the difference and how do they work together?

People often confuse Excel’s LAMBDA and SCAN functions because they often appear together in formulas. However, they are not the same: LAMBDA is the “brain” that defines the logic, and SCAN is the “vehicle” that carries it out. Here’s how to tell them apart.

LAMBDA creates custom logic

The reason Excel’s LAMBDA function is so revolutionary is because it’s a blank page. Most Excel functions have a fixed goal: SUM always adds and AVERAGE always finds the average. LAMBDA, however, doesn’t “do” anything until you give it a set of instructions.

In the formula below, LAMBDA doesn’t have any numbers to work with yet, which is why it returns the #CALC! error:

=LAMBDA(a,b,a*b)

However, he knows that when he do receive two values ​​(A And b), you have to multiply them (a*b). That’s why I call it the “brain”: it has the intelligence, but it needs an external source to feed it with data.

The autonomous power of LAMBDA

You can distinguish LAMBDA from SCAN because it can live independently in the name manager. By saving a LAMBDA and giving it a name, like “CalculateMarkup”, you effectively create a new native Excel function that you can call anywhere in your workbook. SCAN, on the other hand, cannot be saved in this way: it must live inside a formula.

Related

How to use LAMBDA in Excel to create your own functions

Simplify and reuse your most complex formulas.

SCAN scans the data range

While LAMBDA is about logic, SCAN is about movement. This is a helper function designed to step through a range of cells while keeping track of a running total or increasing list. As a result, he lacks the intelligence necessary to perform calculations; he only knows how:

1. Start at a specific value.

2. Cycle through a range of cells.

3. Ask a LAMBDA for instructions at every step.

For this reason, it follows this syntax:

=SCAN([initial_value],array,LAMBDA)

Or:

• initial_value is the starting point.
• painting is the table to analyze.
• LAMBDA is the function argument that tells SCAN what to do.

Both functions work as a team

The confusion between LAMBDA and SCAN comes from the fact that SCAN is almost always the parent function in the formula bar:

=SCAN(0,T_Sales[Sales],LAMBDA(a,b,a+b))

Since the LAMBDA is placed between the SCAN parentheses, it is easy to think of them as a single tool. However, associating them in this way causes a “handshake” in each cell:

1. SCAN retrieves the current total and the new cell value.

2. SCAN transmits both data elements to LAMBDA.

3. LAMBDA performs the calculation and returns the result.

4. SCAN writes this result and moves to the next line to start the process again.

LAMBDA and SCAN handle more than just math

To see the distinction between these functions in a practical scenario, I’ll show you how they handle text strings. While a simple sum may make them seem like a single mathematical tool, using them to create a breadcrumb of text reveals how they actually interact.

Imagine you have a column of phonetic codes and you want to create a running log in column C.

To do this, you would use the following formula:

=SCAN("",T_NATO[Phonetic],LAMBDA(a,b,IF(b="",a,IF(a="",b,a&" - "&b))))

In this case, the functions perform two distinct tasks:

• SCANNING handles the starting state (the empty text string “”) and physical movement through the code range. Its only job is to provide the “where” and “when”.
• LAMBDA evaluates the contents of each individual cell and makes logical decisions that SCAN cannot. First, it checks if a cell is empty to avoid adding malicious separators. If so, it simply advances the current list. However, if the cells contain data, it decides whether to start the list with a single entry or add a new word to the existing string.

The result is a sparse list that grows with each line. This proves that SCAN is the delivery system for any custom logic dictated by LAMBDA.

Related

Tired of Excel’s #SPILL! error? Here are 5 easy solutions

Quickly resolve this frustrating error by changing the structure of your spreadsheet.

LAMBDA and SCAN create more robust totals

Most people stumble upon this match made in heaven when building a running total. In older versions of Excel, you probably used a formula like =SUM($B$2:B2) and dragged it to the bottom of the column.

The problem with this traditional method is that it relies on relative references. If you sort your data, insert a new row, or delete a cell in the middle of the range, the references often break or return #REF! errors. By using LAMBDA and SCAN together, you create a single dynamic table that only exists in a single cell.

Take this formula:

=SCAN(0,T_Inventory[Stock],LAMBDA(a,b,a+b))

Here, the functions share responsibility for avoiding errors:

• SCANNING locks the calculation to the range. Since this is a single formula, you don’t have to worry about changing or deleting individual cell formulas in the middle of the column.
• LAMBDA holds logic. If you need to change the calculations, just do it in one place to update the entire column.

Once you recognize that LAMBDA is the intelligence and SCAN is the transmission system, you can stop treating them as a single, confusing unit. This distinction is the key to moving away from flimsy, drop-down formulas and toward robust, automated spreadsheets that update as data grows.

However, linking these two functions is just one way of handling tables in modern Excel. If you don’t need to accumulate a running total and simply want to transform each cell in a range individually, you should use the MAP function instead.

Operating system

Windows, macOS, iPhone, iPad, Android

Free trial

1 month

Microsoft 365 includes access to Office apps like Word, Excel, and PowerPoint on up to five devices, 1TB of OneDrive storage, and more.