The purpose of this article is to explain how to use the Excel MAP, REDUCE, and SCAN functions with dynamic arrays and LAMBDA so that advanced users can build faster, cleaner, and more scalable formulas in real-world workbooks.
1. What are MAP, REDUCE, and SCAN in Excel?
MAP, REDUCE, and SCAN are advanced dynamic array helper functions in modern Excel that work together with LAMBDA to process ranges and arrays in a functional way.
1.1 High-level roles of each function
- MAP applies a LAMBDA function to every element in one or more arrays and returns a transformed array with the same shape.
- REDUCE iterates through an array and accumulates all values into a single result, such as a custom total or composite value.
- SCAN is similar to REDUCE but returns all intermediate accumulated values, making it ideal for running totals, running counts, and step-by-step calculations.
All three functions are dynamic array formulas: they spill results to adjacent cells and automatically resize as the source data expands.
1.2 Basic syntax of each function
The MAP function syntax is:
=MAP(array1, [array2], [array3], ..., LAMBDA(parameter1, [parameter2], ..., calculation)) It maps the LAMBDA over each element in the provided arrays and returns an array result.
The REDUCE function syntax is:
=REDUCE([initial_value], array, LAMBDA(accumulator, value, calculation)) It walks through array, updating accumulator on each step, and finally returns the last accumulator as a single result.
The SCAN function syntax is:
=SCAN([initial_value], array, LAMBDA(accumulator, value, calculation)) SCAN uses the same pattern as REDUCE but returns an array of accumulator values at each step instead of only the final one.
2. Prerequisites and version considerations
MAP, REDUCE, and SCAN are available in Microsoft 365 and recent subscription-based versions of Excel, including Excel for the web, and are not generally available in perpetual versions like Excel 2016 or Excel 2019.
- Ensure your Excel build supports dynamic arrays.
- Ensure the LAMBDA function is available, as these helpers are designed to work with it.
- If your formulas do not spill and produce
#NAME?errors, your Excel version is likely too old for these functions.
3. Conceptual model: thinking functionally in Excel
To use MAP, REDUCE, and SCAN effectively, it helps to think in terms of functional programming concepts:
- Array in, array out. You operate on entire arrays rather than copying formulas down rows.
- Pure transformations. A LAMBDA takes inputs and returns a result, without relying on external state.
- Composition. You can nest MAP, REDUCE, SCAN, FILTER, SORT, UNIQUE, and other dynamic array functions to build powerful pipelines.
4. MAP function: transforming arrays without helper columns
MAP is ideal for situations where you would normally add helper columns, fill formulas down, and then reference those helper results. With MAP, you can encapsulate the transformation in a single dynamic array formula.
4.1 Simple example: add VAT to prices
Suppose you have net prices in B2:B11 and you want to calculate prices with 10% VAT as a spilled result:
=MAP(B2:B11, LAMBDA(price, price * 1.1)) This formula returns an array of VAT-inclusive prices with no need to copy formulas down.
4.2 Conditional transformation: label grades
Assume scores in C2:C21. You can map scores to letter grades:
=MAP(C2:C21, LAMBDA(score, IF(score >= 90,"A", IF(score >= 80,"B", IF(score >= 70,"C", IF(score >= 60,"D","F")))))) The MAP function applies the LAMBDA to each score, returning an array of grade labels in one step.
4.3 Multi-array example: combine first and last name
MAP can consume multiple arrays. For example, with first names in A2:A11 and last names in B2:B11:
=MAP(A2:A11, B2:B11, LAMBDA(first, last, last & ", " & first)) This makes it easy to build formatted labels without helper columns.
4.4 Using MAP with complex logic and named LAMBDAs
For maintainability, move complex logic into a named LAMBDA and then call it from MAP. For example, define a named LAMBDA function NormalizeAmount:
=LAMBDA(amount, currency, LET( rate, CHOOSE(MATCH(currency,{"USD","EUR","KRW"},0),1,1.1,0.0009), amount * rate ) ) Then apply it over transaction arrays:
=MAP(Amounts, Currencies, LAMBDA(a, c, NormalizeAmount(a, c))) 5. REDUCE function: custom aggregations beyond SUM
REDUCE is used when you want a single result from an array but the logic is more complex than a simple SUM, AVERAGE, or COUNT.
5.1 Syntax pattern
=REDUCE(initial_value, array, LAMBDA(accumulator, value, <calculation using accumulator and value>)) Excel passes each element in array to the LAMBDA, updating the accumulator on each step and returning the final accumulator as the result.
5.2 Example: conditional sum with complex logic
Consider ranges:
Amounts: numeric values.Categories: category for each amount.IncludeList: categories you want to include in the total.
You want to sum only amounts whose category appears in IncludeList, but with a 20% uplift for category "A". REDUCE allows you to express this directly:
=REDUCE( 0, SEQUENCE(ROWS(Amounts)), LAMBDA(acc, i, LET( amt, INDEX(Amounts, i), cat, INDEX(Categories, i), inList, ISNUMBER(MATCH(cat, IncludeList, 0)), adjusted, IF(cat="A", amt*1.2, amt), IF(inList, acc + adjusted, acc) ) ) ) This approach avoids helper columns and preserves the full logic in one transparent formula.
5.3 Example: concatenating strings with custom separators
REDUCE can also build strings, not only numeric totals. For example, to join product codes in A2:A10 with semicolons, skipping blanks:
=REDUCE( "", A2:A10, LAMBDA(acc, value, IF(value="", acc, IF(acc="", value, acc & "; " & value ) ) ) ) The final accumulator is a single text string containing all non-blank product codes.
5.4 Example: building a JSON-like structure
For advanced reporting, REDUCE can accumulate a text structure like JSON based on row data. Combined with LET and text functions, you can generate machine-readable payloads directly from Excel.
6. SCAN function: running totals and step-by-step calculations
SCAN works like REDUCE but exposes every step of the accumulation as an array. This is particularly useful for time series analysis, inventory balances, and amortization schedules where intermediate values matter.
6.1 Basic running total with SCAN
Given daily sales in B2:B31, a running total can be created with:
=SCAN(0, B2:B31, LAMBDA(acc, value, acc + value)) The result spills down with the cumulative sum for each day.
6.2 Running balance with deposits and withdrawals
Suppose column B contains positive and negative values representing deposits and withdrawals, and you want a running account balance starting from an initial opening balance in F1:
=SCAN(F1, B2:B50, LAMBDA(balance, txn, balance + txn)) This returns the balance after each transaction, which is much cleaner than copying a formula down an entire column.
6.3 Advanced example: running maximum
You can also use SCAN to compute a running maximum (useful for monitoring peak utilization, maximum drawdowns, or SLA breaches):
=SCAN(-1E+99, B2:B50, LAMBDA(maxSoFar, value, MAX(maxSoFar, value))) Each spilled cell shows the maximum value encountered up to that point.
6.4 Using SCAN for stepwise business rules
SCAN is especially powerful when business rules depend on previous results, such as tiered discounts applied over time or inventory that changes based on prior balance and new orders. Instead of using circular references or complex helper columns, SCAN allows you to express the dependency through the accumulator.
7. Comparing MAP, REDUCE, and SCAN
| Function | Main purpose | Input | Output | Typical use case |
|---|---|---|---|---|
| MAP | Transform each element of one or more arrays. | One or more arrays. | Array with same dimensions as input. | Scaling numbers, formatting text, mapping codes to labels. |
| REDUCE | Aggregate an array to a single result. | Single array plus initial value. | Single scalar value. | Custom totals, complex conditional sums, concatenations. |
| SCAN | Return each step of an accumulation. | Single array plus initial value. | Array of intermediate results. | Running totals, running balances, cumulative metrics. |
8. Combining MAP, REDUCE, SCAN with LET and other dynamic array functions
The real power of these functions appears when you combine them with LET, FILTER, SORT, UNIQUE, BYROW, and BYCOL in a single formula pipeline.
8.1 Pattern: FILTER → MAP → REDUCE
Consider a scenario where you have a transactions table and want to compute a custom risk score over filtered rows:
- Use
FILTERto isolate relevant rows (e.g., transactions in a given period). - Use
MAPto calculate a per-transaction risk score from multiple columns. - Use
REDUCEto aggregate all risk scores into a single total risk index.
=LET( tx, FILTER(Transactions, (Transactions[Date]>=StartDate) * (Transactions[Date]<=EndDate)), scores, MAP( CHOOSECOLS(tx,1), CHOOSECOLS(tx,2), LAMBDA(amount, type, IF(type="High", amount*2, amount) ) ), REDUCE(0, scores, LAMBDA(acc, s, acc + s)) ) 8.2 Pattern: SCAN for dynamic schedules
For amortization schedules or inventory levels, SCAN can replace entire blocks of formulas. For example, to create a reducing balance for a loan based on principal in B2, interest rate in B3, periodic payment in B4, and periods in B5:
=LET( principal, B2, rate, B3, payment, B4, n, B5, periods, SEQUENCE(n), SCAN(principal, periods, LAMBDA(balance, k, balance*(1+rate) - payment ) ) ) The spilled result is the balance after each period without needing row-by-row formulas.
8.3 Pattern: MAP + BYROW/BYCOL for matrix operations
When working with matrices, use BYROW or BYCOL to pre-aggregate data and then MAP to apply logic per row or column. For example, to apply a scoring LAMBDA to each row of an input table:
=BYROW(InputTable, LAMBDA(row, MAP(row, LAMBDA(cell, IF(cell="Y",1,0) ) ) ) ) This returns a matrix of row-wise scores derived from the original table.
9. Performance and maintainability best practices
- Use LET extensively. Store intermediate arrays and scalar values in LET variables to avoid recomputing them, which improves performance and readability.
- Encapsulate logic in named LAMBDAs. Move complex logic into reusable LAMBDAs and call them from MAP, REDUCE, and SCAN to make formulas easier to audit.
- Avoid volatile functions inside MAP/REDUCE/SCAN. Volatile functions like OFFSET and INDIRECT can cause recalculation overhead when used inside iterative helpers.
- Test logic on small arrays first. Before applying to large ranges, test the LAMBDA and helper function on a small sample to confirm behavior.
- Document parameters. When creating named LAMBDAs, store descriptions in a separate documentation sheet for maintainability.
10. Practical checklist for using MAP, REDUCE, and SCAN in real workbooks
- Replace repetitive row-based formulas with MAP whenever you see “copy down” patterns.
- Replace multi-step helper-column aggregations with a single REDUCE formula when the end result is a single number or string.
- Replace manual running total columns with SCAN to manage any cumulative process.
- Standardize complex business rules as named LAMBDAs, then reuse them via MAP and SCAN across multiple reports.
- Combine FILTER, SORT, and UNIQUE with MAP/REDUCE/SCAN to create advanced, fully dynamic dashboards that adjust automatically to input changes.
FAQ
When should I use MAP instead of just writing a regular array formula?
Use MAP when you need to apply the same logic to each element of one or more arrays without referencing external cells or writing multiple helper formulas. If your logic is simple and uses built-in functions directly on a range, a standard dynamic array formula may be enough. Once the logic becomes complex or you want to reuse it, wrapping it in MAP with a LAMBDA is usually cleaner and easier to maintain.
How is REDUCE different from SUM, AVERAGE, or other aggregation functions?
Standard aggregation functions compute predefined statistics like sum, average, or count. REDUCE is a generic aggregator: it allows you to define any accumulation logic, including conditional rules, text concatenation, or multi-step scoring. If a calculation cannot be expressed as a simple built-in aggregate, REDUCE is the flexible alternative.
Can I use SCAN to build a full amortization or inventory schedule?
Yes. SCAN is explicitly designed to return intermediate accumulation results, which is exactly what you need for amortization, inventory flows, and other running-balance problems. You express the transition from one period to the next inside the LAMBDA, and SCAN spills the resulting balance for each period in a single array.
What happens if my LAMBDA in MAP, REDUCE, or SCAN has the wrong number of parameters?
If the LAMBDA signature does not match the expected number of parameters for MAP, REDUCE, or SCAN, Excel returns a #VALUE! error indicating incorrect parameters. For MAP, the LAMBDA must accept a parameter for each array argument. For REDUCE and SCAN, the LAMBDA must accept two parameters: an accumulator and the current value.
Are MAP, REDUCE, and SCAN safe to use in large workbooks from a performance perspective?
They are generally efficient, especially when combined with LET and well-structured LAMBDAs. However, as with any dynamic array formula, you should avoid overly complex logic on very large ranges, and you should minimize recalculation triggers by avoiding volatile functions. In many cases, replacing thousands of copied formulas with a single helper-based formula can actually improve performance.