MAP in Excel: Transform Values and Combine Aligned Arrays
Use MAP and LAMBDA to transform numeric values, calculate aligned order lines and classify rows without confusing mapping with aggregation.
Verification status: See the evidence and test scope described in this guide. How formula examples are checked
Quick answer: MAP applies a LAMBDA calculation to corresponding values in one or more arrays. It returns the per-position results, making it useful for custom conversions and rules that combine aligned inputs.
Availability and version history
- First introduced: Preview announced July 26, 2021, for Windows Beta 2108 (build 14312.20008) and Mac 16.52 (build 21072100). These are announced preview minima. Official introduction.
- Current documented status: the dedicated help lists Excel for Microsoft 365, Excel for Microsoft 365 for Mac, Excel 2024, Excel 2024 for Mac. Availability depends on the installed build.
- Evidence checked: October 14, 2026. Official MAP help. Examples below were independently calculated, not executed in Excel.
Syntax and parameter count
=MAP(array1,[array2,...],LAMBDA(value1,[value2,...],calculation))
This expanded notation describes the documented argument pattern: supply arrays first and the LAMBDA last. The LAMBDA needs one named parameter for each input array. Microsoft documents #VALUE! with incorrect parameters for an invalid signature. MAP reference
For two input ranges, use two parameters such as units and price. Do not confuse their positions with worksheet column letters. These formulas use comma separators in an English-language locale.
Prepare an order-line table
Enter A1:D4:
| Item | Units | Unit price | Status |
|---|---|---|---|
| Pen | 2 | 3 | Paid |
| Pad | 5 | 4 | Pending |
| Box | 1 | 12 | Paid |
Use F2 onward for output. B2:C4 must contain numbers; D2:D4 contains text.
Example 1: transform a single list
=MAP(B2:B4,LAMBDA(units,units*10))
Expected vertical results: 20, 50, 10. Each value is multiplied independently. The result has three entries; MAP has not added them together.
For multiplication alone, =B2:B4*10 may be simpler in a dynamic-array workbook. MAP becomes more useful when the per-item logic is complex or should be expressed as a named calculation.
Example 2: combine two aligned ranges
=MAP(B2:B4,C2:C4,LAMBDA(units,price,units*price))
Expected vertical results: 6, 20, 12. The first units value is combined with the first price, the second with the second, and so on. The total of these line amounts is 38, but the MAP formula itself returns the three individual amounts.
This is positional matching. It does not search for an item code. If the price list is in a different order, perform a lookup first rather than assuming matching row positions.
Example 3: calculate paid amounts only
=MAP(B2:B4,C2:C4,D2:D4,LAMBDA(units,price,status,IF(status="Paid",units*price,0)))
Expected output: 6, 0, 12. The Pending row is deliberately assigned zero according to this example’s rule.
For a final paid total, wrap the calculation in SUM:
=SUM(MAP(B2:B4,C2:C4,D2:D4,LAMBDA(units,price,status,IF(status="Paid",units*price,0))))
Expected result: 18. Keeping the unaggregated output during debugging helps show which rows contributed to the total.
Advanced example: tiered handling charges
For each order, charge 2 when units are below 3 and 5 otherwise:
=MAP(B2:B4,LAMBDA(units,IF(units<3,2,5)))
Expected results: 2, 5, 2. The comparison is per order, so it does not use the combined quantity of eight units. At the exact boundary of 3, the charge is 5 because the condition is strictly less than 3.
Try a separate boundary list of 2, 3 and 4. Its expected charges are 2, 5 and 5. Boundary tests are often more informative than repeating several values on the same side of a threshold.
Avoid shape and data-type mistakes
Use aligned ranges of the same shape in these examples. An omitted or extra LAMBDA parameter is a different issue from bad source data. Keep the LAMBDA last; a range placed after it is not another valid input to the same calculation.
MAP’s per-position calculation should return the intended single result for that position. A formula that tries to create a separate multi-cell array for each item is a different array-construction problem.
Do not apply a universal IFERROR wrapper until you have checked whether an error signals a misaligned range, an invalid price or a missing function. Zero may be a valid business value, so using it as an error placeholder can conceal the problem.
Related guides
Verification: Inputs are fictional. Expected values and array shapes were independently calculated from these inputs; no native Excel execution or screenshot is claimed. See the editorial testing standards.