MAP, REDUCE, SCAN and MAKEARRAY: applying a LAMBDA across an array — and knowing when you actually need to

FAQ — What does this article answer?

Q: What do these four functions do?

A: They are drivers: each takes a LAMBDA, an operation that you define, and applies it to an array. MAP transforms every element and returns an array of the same shape. REDUCE collapses an array into a single accumulated value. SCAN performs the same accumulation, but returns the result at every step. MAKEARRAY builds an array of any size, computing each cell from its row and column number.

Q: What is a LAMBDA, and why do these need one?

A: A LAMBDA is a formula whose inputs you name. For example LAMBDA(x, x*0.05) takes x and returns x*0.05. On its own, it does nothing. It needs something to call it. These four functions are exactly that: they repeatedly call your LAMBDA repeatedly across your data. Part 1 is a brief recap.

Q: When do I actually need these rather than a plain spilled formula?

A: Less often than you might think and that is the most important point of this article. A direct array formula such as =A2:A100*B2:B100 already spills across the whole range. You should only use these functions only when the per-element logic is too complex for a direct formula, when you need a running or accumulated result, or when you are building an array from its coordinates. Part 8 draws the line.

Q: Which version of Excel have them?

A: MAP, REDUCE, SCAN and MAKEARRAY, along with LAMBDA, BYROW and BYCOL, are available in Microsoft 365, Excel for the web and for Excel 2024 (Windows and Mac). They are not in Excel 2021, 2019 or 2016, where they return #NAME?.

Q: What is the difference between REDUCE and SCAN?

A: They use the same accumulator and the same LAMBDA. REDUCE only returns the final value, the endpoint. SCAN returns the value after each step, the whole path. A running total is SCAN; REDUCE is just the final figure.

Most of Excel's function library is first-order: which means a function takes values and returns a value. This small family is bit different. Just so you know MAP, REDUCE, SCAN and MAKEARRAY are higher-order functions. This means they take a function as an argument and apply it to an array. This is a real change in what a formula can do: instead of writing out a calculation once, you hand these functions the calculation as a LAMBDA and let them do the work across your data.

This makes them the most powerful part of the dynamic-array toolkit, but also the one you're most likely to overuse. It's really important to get your head around these ideas, because they'll tell you when to use them and, just as importantly, when a plain spilled formula is the better answer. This article explains the ideas behind each of the four, then gives its verdict.

Part 1: Here's a quick reminder about LAMBDA.

All four functions consume a LAMBDA, so it's worth spending 30 seconds on which one is best. A LAMBDA basically turns a calculation into a reusable function by naming its inputs:

=LAMBDA(x, x*0.05)

This defines a function that takes one input, x, and returns x * 0.05. It's just a single cell, so it's got no inherent value as it's not possible to work on it yet. There are two ways you can use it: you can give it a name in the Name Manager and call it like any built-in function, or you can hand it to a driver such as MAP that supplies the values. The four functions in this article are the ones that the drivers use. If you want to know more about LAMBDA itself, including recursion, check out Recursive LAMBDA functions in Excel.

Part 2: The family, a rundown of the four drivers and how much they've made

The best way to keep the four in mind is to think about the shape of what each one returns.

MAP takes an array and returns another array with the same shape, but with each element changed. So, when you use the REDUCE function, you can give it an array and it'll give you back one value, the whole thing collapsed. So, SCAN takes an array and gives you another array of the same size, but each element is the total result so far. MAKEARRAY doesn't take any input array, just a number of rows and columns. It'll then build a grid and calculate each cell's position. Two close cousins round out the set: If you use BYROW or BYCOL, they'll run a LAMBDA on each whole row or column instead of each cell, and they'll return one result for each row or column.

Part 3: The data we're working with

These examples show you what I mean using a table of monthly sales figures. The amounts in the example are £8,000; £12,000; £15,000; £9,500; £18,000; £11,000. You'll find this table (called 'Sales') somewhere in your Excel file. If you're using a named range or table reference, it'll make your formulas easier to read, as I explain in Why every dataset should be an Excel table.

Part 4: MAP, we're going to be looking at transforming every single element

MAP basically applies a LAMBDA to each element of an array and returns a new array of the same size.

=MAP(array1, [array2], ..., lambda)

Let's say commission is structured like this: 5% on sales up to £10,000, 10% on anything above that. Just write one formula for each month:

=MAP(Sales, LAMBDA(s, IF(s>10000, s*0.1, s*0.05)))

The LAMBDA takes one month's figure as s and gives you that month's commission; MAP runs it down the whole column and spills the result.

Here's the thing: this whole family is governed by one simple rule. You don't need to use MAP for simple element-wise arithmetic, =Sales * 0.05 works across the whole range. MAP is useful when the logic is more complex than you can fit into a simple array formula, like when you've got multiple conditions, a LET with intermediate steps, or a named LAMBDA you want to use. If a basic array formula works, go for it.

Part 5: REDUCE and SCAN, accumulating across an array

You really need to understand these two together, because they use the same mechanism but differ in exactly one respect. They both walk through an array carrying an accumulator, a running value passed from one element to the next, and both take a LAMBDA(accumulator, value, …). The difference is what they hand back.

=REDUCE([initial_value], array, lambda(accumulator, value, body))
=SCAN([initial_value], array, lambda(accumulator, value, body))

REDUCE returns only the final accumulator, the endpoint:

=REDUCE(0, Sales, LAMBDA(acc, v, acc+v))     ' returns 73,500

SCAN returns the accumulator after every step, the whole path:

=SCAN(0, Sales, LAMBDA(acc, v, acc+v))       ' returns 8,000; 20,000; 35,000; 44,500; 62,500; 73,500

That's the whole difference: a running total is SCAN; its closing figure alone is REDUCE. The initial_value sets the accumulator's starting point, 0 for a sum, and, when you're building a text result, "".

The best thing about REDUCE is that it generalises SUM. Summing is just the simplest way to add things up, and the accumulator can be used for any rule. To add up a series of monthly growth rates into one total, something no single native function does easily, multiply into the accumulator instead of adding, starting from 1:

=REDUCE(1, GrowthRates, LAMBDA(acc, r, acc*(1+r)))     ' e.g. +8%, +5%, -3%, +6% → 1.166

Part 6: MAKEARRAY, how to build an array from its coordinates

MAKEARRAY is different from the rest because it doesn't take a source array. So, you just specify the size, and then LAMBDA figures out each cell using its row and column number

=MAKEARRAY(rows, columns, lambda(row, column, body))

The LAMBDA part gets the current cell's row and column index. A four-by-four multiplication table is just the product of the two:

=MAKEARRAY(4, 4, LAMBDA(r, c, r*c))

The cell at row 3, column 4 is 3 * 4 = 12. When it comes to business use, this coordinate-driven construction is how you generate a scenario grid without a Data Table, payment for each combination of rate and term, for exmple or lay out a calendar, or generate a structured sequence of values. If the value of a cell depends on its location, then MAKEARRAY is the tool for the job.

Part 7: Composing and reusing — LET and named LAMBDAs

There are two ways to go about this. You can turn these functions from clever one-liners into maintainable tools. So, the first one is LET. If you put LET inside a LAMBDA, it makes the intermediate steps easier to read. This is because it keeps the complex per-element calculation readable, rather than making it a dense nest. This is explained in more detail in LET: cleaner, faster formulas.

The second is reuse. Once a LAMBDA has shown what it can do, it can be used in more than one formula. Just define it once in the Name Manager, Commission = LAMBDA(s, IF(s>10000, s*0.1, s*0.05)). and it becomes a function you can call anywhere in the workbook, including inside MAP: =MAP(Sales, Commission). This is where the toolkit meets the wider ecosystem: the higher-order functions apply the logic, and a named LAMBDA lets a team share and maintain that logic behind a friendly name. If you want to know more about the mechanics and the pitfalls of naming, check out The Name Manager: named ranges, benefits and pitfalls.

Part 8: Picking the right level of abstraction is key.

This is the conclusion the article has been leading up to. People rarely ask, "Which of these four?" Instead, they ask, "Do I need one at all? And if so, what level should it be?"

First, try using a direct array formula. When the operation is element-wise and simple, Excel already spills it, like =A2:A100*B2:B100, =IF(Sales>0, "Gain", "Loss"), so there's no need for a driver at all. This is the clearest and easiest option to maintain, and it should be the default. You can find more about the dynamic-array behaviour it relies on in Dynamic arrays with FILTER, SORT, UNIQUE and SEQUENCE.

You should move up to the higher-order functions when a direct formula can't express the job. This could be because per-element logic is complex or multi-step, you need a running or accumulated result (SCAN, REDUCE), or you build an array from its coordinates (MAKEARRAY). The test is whether the alternative would be a sprawl of helper columns, if so, one of these functions is the cleaner answer.

You can lift the logic into a named LAMBDA when the same calculation repeats across the workbook, when you want a reusable function with a meaningful name, or when a team needs to share and maintain it. The higher-order function is still the one that does the applying; it's the name that gives the logic a home.

When it comes to availability, this family follows the modern dynamic-array pattern: Microsoft 365, Excel for the web and the perpetual Excel 2024, but not Excel 2021 or earlier, where they return #NAME?; the full picture is in Excel 365 versus older versions. If you need to do a lot of heavy iteration or process external data, Power Query or VBA are the best tools for this job. These functions are for in-sheet, formula-driven work, the self-updating pattern described in Automating reports with dynamic arrays and tables.

Part 9: Common failure modes

If a nested LAMBDA doesn't behave as it should, the Evaluate Formula tool will go through it one calculation at a time. You can find more information on this in Analysing a formula for better understanding.

Summary

These four functions bring higher-order thinking to the grid: you supply a LAMBDA, and they apply it across an array.

• MAP transforms every element and returns a same-shape array; use it when the per-element logic is too rich for a plain spilled formula.
• REDUCE collapses an array to one accumulated value, SUM, generalised to any rule, such as compounding.
• SCAN shares REDUCE's accumulator but returns every step, the running total to REDUCE's closing figure.
• MAKEARRAY builds a grid of any size, computing each cell from its row and column number.
• Choose the level of abstraction deliberately: a direct array formula first, a higher-order function when the logic demands it, a named LAMBDA when it should be reused.
• They run in Microsoft 365, Excel for the web and Excel 2024, not Excel 2021 or earlier, where they return #NAME?.

The idea is the same one that's used in pretty much everything these days: match the tool to the problem, go for the simplest expression that works, and only use the powerful abstraction when a simpler one just won't cut it.

See also: Recursive LAMBDA functions in Excel for the LAMBDA these functions drive, Dynamic arrays with FILTER, SORT, UNIQUE and SEQUENCE for the spill behaviour they build on, LET: cleaner, faster formulas for keeping a complex LAMBDA readable, The Name Manager: named ranges, benefits and pitfalls for turning a LAMBDA into a reusable named function, Automating reports with dynamic arrays and tables for the self-updating report pattern, and Excel 365 versus older versions for the full availability picture.

This article is part of the helpme.safeoffice.de series, which provides practical guides on Excel functions, workbook modeling and data solutions. The series is aimed at businesses that want effective, maintainable tools that everyone can understand.