The Excel MAP function applies a LAMBDA to every element of an array and spills back an array of the same shape, transformed. It is the “do this to each value” tool — the formula that replaces a helper column you’d otherwise drag down 500 rows. Feed it two or more arrays and it zips them together, passing matching elements into a multi-parameter LAMBDA.
p, evaluates the calculation, and returns the results in the same 5-row shape.
Syntax
| Argument | Description | |
|---|---|---|
array1 | Required | The range or array whose elements you want to transform. The result has the same dimensions. |
array2, … | Optional | Additional arrays of the same size. Element 1 of every array goes into the LAMBDA together, then element 2, and so on. |
lambda | Required | A LAMBDA with exactly one parameter per array. With one array it receives one element; with three arrays it must declare three parameters. |
Available in: Excel for Microsoft 365 and Excel for the web only. Older versions show #NAME?. The result is a dynamic array that spills.
Transform every value in a column
Prices sit in B2:B6. One formula in C2 returns the taxed price for every row — no fill handle, no helper column to maintain:
| A | B | C | |
|---|---|---|---|
| 1 | Item | Price | With tax |
| 2 | Notebook | 4.99 | 5.40 |
| 3 | Desk lamp | 24.50 | 26.52 |
| 4 | Cable kit | 12.75 | 13.80 |
| 5 | Monitor arm | 89.00 | 96.34 |
| 6 | USB hub | 19.99 | 21.64 |
The LAMBDA’s parameter receives one element at a time — a single price, not the whole range. Add a row to the source and re-point the range (or use a Table) and the spill grows with it.
Text works the same way:
Try it: interactive MAP demo
Pick a transformation and watch MAP apply it to every price in the list.
Zip multiple arrays into one LAMBDA
Pass two arrays and declare two parameters — MAP feeds matching elements in pairs. Quantity times price, element by element:
Element-wise logic that would otherwise need a clumsy nested IF over two ranges:
MAP also nests cleanly inside other dynamic-array functions:
Choosing a helper: MAP transforms each element. If you need one result per whole row or column, that’s BYROW / BYCOL; one running total, SCAN; one single value, REDUCE; a brand-new grid from indexes, MAKEARRAY.
Errors & common pitfalls
#CALC! — parameter count doesn’t match array count. Two arrays need a two-parameter LAMBDA: =MAP(A2:A9, B2:B9, LAMBDA(a, a*2)) fails because the second array’s elements have nowhere to go. Declare one parameter per array, in order.
#VALUE! — arrays are different sizes. Zipping A2:A10 with B2:B8 fails — every array must have identical dimensions so elements pair up one-to-one.
#NAME? — older Excel. MAP (and LAMBDA itself) exist only in Excel for Microsoft 365 and Excel for the web. There is no equivalent in Excel 2021 or earlier.
Pitfall: the LAMBDA gets elements, not ranges. Inside MAP, the parameter is a single value, so aggregations like SUM(p) just return that one value. If your instinct is “SUM each row,” you want BYROW, which passes whole vectors instead.
Pitfall: #SPILL! from a blocked landing zone. MAP returns an array the same size as the input — it needs that many empty cells below/beside the formula.
Practice workbook
Frequently asked questions
What does the Excel MAP function do?
How do I use MAP with two arrays?
=MAP(B2:B9, C2:C9, LAMBDA(qty, price, qty*price)). MAP zips them — first elements together, second elements together, and so on. The arrays must be the same size.What's the difference between MAP and BYROW?
Why does MAP return #CALC!?
Which Excel versions support MAP?
Master functions like this in one day
This page covers one function. Our Excel Formulas and Functions class covers the 30 that matter most — live, hands-on, taught by professionals in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.
See the Formulas & Functions Class