People often confuse Excel’s LAMBDA and SCAN functions because they often appear together in formulas. However, they aren’t 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.
The LAMBDA and SCAN functions are available to those using Excel 2024 or later, Excel for Microsoft 365, Excel for the web, or the most up-to-date Excel mobile and tablet apps.
LAMBDA creates the custom logic
The reason Excel’s LAMBDA function is so revolutionary is that it’s a blank slate. Most Excel functions have a fixed purpose—SUM always adds, and AVERAGE always finds the mean. LAMBDA, however, doesn’t “do” anything until you give it a set of instructions.
In the formula below, LAMBDA doesn’t yet have any numbers to work with, which is why it returns the #CALC! error:
=LAMBDA(a,b,a*b)
However, it knows that when it does receive two values (a and b), it should multiply them (a*b). This is why I call it the “brain”—it holds the intelligence, but it needs an outside source to feed it data.
The standalone power of LAMBDA
You can tell LAMBDA apart 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, can’t be saved 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 navigates the data range
While LAMBDA is about logic, SCAN is about movement. It’s a helper function designed to travel through a range of cells while keeping track of a running total or a growing list. As a result, it lacks the intelligence to perform math—it only knows how to:
- Start at a specific value.
- Move through a range of cells.
- Request instructions from a LAMBDA at every step.
For this reason, it follows this syntax:
=SCAN([initial_value],array,LAMBDA)
where:
- initial_value is the starting point.
- array is the array to be scanned.
- LAMBDA is the function argument that tells SCAN what to do.
The two functions work as a team
The confusion between LAMBDA and SCAN arises because SCAN is almost always the parent function in the formula bar:
=SCAN(0,T_Sales[Sales],LAMBDA(a,b,a+b))
While using Excel tables for the source data is best practice, the SCAN formula must be placed outside the table. This is because it produces a spilled array, which tables can’t handle. However, note that you should also leave a buffer column between the table and the SCAN formula so that the table doesn’t grab the resultant array when you press Enter.
Because the LAMBDA is tucked inside the SCAN parentheses, it’s easy to think of them as a single tool. However, pairing them in this way causes a “handshake” in every cell:
- SCAN picks up the current total and the new cell value.
- SCAN hands both pieces of data to LAMBDA.
- LAMBDA performs the calculation and hands the result back.
- SCAN writes that result down and moves to the next row 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 might make them seem like a single mathematical tool, using them to build a breadcrumb trail of text reveals how they actually interact.
Imagine you have a column of phonetic codes and 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 are performing two separate tasks:
- SCAN manages the starting state (the empty text string “”) and the physical movement through the range of codes. Its only job is to provide the “where” and the “when.”
- LAMBDA evaluates the content of each individual cell and makes logical decisions that SCAN cannot. First, it checks if a cell is empty to avoid adding rogue separators. If it is, it simply carries the current list forward. If the cells contain data, however, it decides whether to start the list with a single entry or append a new word to the existing string.
The result is a spilled list that grows with each row. This proves that SCAN is the delivery system for whatever custom logic the LAMBDA dictates.
Related
Tired of Excel’s #SPILL! error? Here are 5 easy fixes
Quickly resolve this frustrating error by tweaking your spreadsheet’s structure.
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 likely used a formula like =SUM($B$2:B2) and dragged it down 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 array that exists only in a single cell.
Take this formula:
=SCAN(0,T_Inventory[Stock],LAMBDA(a,b,a+b))
Here, functions split the responsibility to prevent errors:
- SCAN locks the calculation to the range. Because it’s a single formula, you don’t have to worry about individual cell formulas in the middle of the column being changed or deleted.
- LAMBDA holds the logic. If you need to change the math, you only have to do so in one place to update the entire column.
Once you recognize that LAMBDA is the intelligence and SCAN is the delivery system, you can stop treating them as a single, confusing unit. This distinction is the key to moving away from fragile, drag-down formulas and toward robust, automated worksheets that update themselves as the data grows.
However, the handshake between these two functions is just one way to handle arrays in modern Excel. If you don’t need to accumulate a running total and simply want to transform every cell in a range individually, you should use the MAP function instead.
OS
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, 1 TB of OneDrive storage, and more.
