Practice free

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.

Practice this in the app

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.

FormulaExcel showsCase
=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 .