MAP function
MAP applies a custom LAMBDA calculation to every element of one or more arrays and returns an array of the same shape. The calculation needs one parameter for each array.
Example
Read it as a sentence: label each number in D2:D7 big when it is over 10 and small when it is not, which gives big.
Syntax
MAP(array1, [array2], ..., lambda)
Arguments in [square brackets] are optional.
-
array1(required) - The first array to loop over.
-
array2(optional) - More arrays of the same size, passed in parallel.
-
lambda(required) - A LAMBDA that takes one item from each array and returns a value.
Returns: An array the same shape as the input, with each item transformed.
One MAP formula can fill more than one cell. When the result has several values, they spill into the cells below or to the right of the formula.
What Excel actually returns
Each formula below was run in desktop Excel. The result is exactly what the cell showed, errors included.
| Formula | Excel shows | Case |
|---|---|---|
=MAP({1,2,3},LAMBDA(x,x*10)) | 10 | One array |
=MAP({1,2},{3,4},LAMBDA(a,b,a+b)) | 4 | Two arrays |
=MAP({1,2},{1,2,3},LAMBDA(a,b,a+b)) | 2 | Different sizes |
=MAP(Items!D2:D7,LAMBDA(x,IF(x>10,"big","small"))) | big | Range |
=MAP(Data!F1:F2,LAMBDA(x,ISBLANK(x))) | TRUE | Blank passed as reference? |
=MAP({1,2},LAMBDA(a,b,a)) | #VALUE! | Too many parameters |
=MAP({1,2},LAMBDA(x,{1,2})) | #CALC! | Lambda returns array |
The Items sheet these formulas read
The Data sheet these formulas read
A cell that starts with = holds that formula.
Watch out for
- The lambda needs one parameter per array.
- If each call returns a single value, output matches the input shape.
Where it works
- Excel
- MAP arrived in Excel 2024. Older versions do not have it. Dynamic-array LAMBDA family; in 365 and Excel for the web, and Excel 2024. Check older perpetual versions.
- Google Sheets
- Same name, same behaviour for everyday use.
Part of Logical functions. Every result on this page was measured in desktop Excel, build 16.0.20527. Last updated .