MAP

Apply a LAMBDA to every value in one or more arrays and return a new array of the results with MAP.

Logical
|
Excel 365 only
|
Google Sheets Supported

Spreadsheet editor

Syntax

=MAP(array1, [array2, ...], lambda) Returns: Array

Arguments

Argument Required Description
array1 Yes The first array or range whose values you want to transform.
array2, ... No Extra arrays to process alongside the first. Every array must have the same number of rows and columns.
lambda Yes A LAMBDA that runs on each value. It must have one parameter for each array you pass and must be the last argument.

About

The MAP function runs a custom calculation on every value in an array and returns a new array of the answers. You pass it one or more arrays, then a LAMBDA that says what to do with each value. MAP walks through the cells one position at a time, hands each value to the LAMBDA, and collects the results into a single spilled array.

Use MAP when you want to transform a whole range without dragging a formula down every row. It shines when your logic is too custom for a built-in function, for example combining two columns, reshaping text, or applying a tiered rule to each value. Because the result is a dynamic array, it resizes itself whenever your source data grows or shrinks.

MAP can take several arrays at once, and the LAMBDA needs one parameter for each array. This makes it a clean way to do element-by-element math across columns, similar to what SUMPRODUCT does, but with full control over the calculation. Pair it with SEQUENCE to generate input numbers, or feed it a trimmed range from FILTER when you only want to transform matching rows.

Examples

Multiply two columns into line totals

Pass two arrays and a two-parameter LAMBDA to build line totals in one formula. Change any units or price value and watch the matching total update.

Spreadsheet editor

Clean a messy column of names

Nest TRIM and UPPER inside the LAMBDA to tidy every name at once. Edit a name to add stray spaces and see MAP clean it up instantly.

Spreadsheet editor

Spot a mismatched-range error

Watch what happens when the two ranges are different sizes: MAP returns a #VALUE! error. Stretch the second range to C2:C5 so both match and the combined totals appear.

Spreadsheet editor

Watch out for

⚠

Parameter count does not match the arrays

You pass two arrays but give the LAMBDA only one parameter (or the other way around), so MAP returns a #VALUE! error.

→ Match them up. The LAMBDA needs exactly one parameter for each array, in the same order you listed the arrays.

⚠

Arrays are different sizes

The arrays you pass have different numbers of rows or columns, and MAP cannot line them up, so you get a #VALUE! error.

→ Make every array the same shape. If one range is shorter, fix the reference so all arrays cover the same number of rows and columns.

⚠

The LAMBDA is not the last argument

You put the LAMBDA before an array or forgot it entirely, and MAP returns an error.

→ Always list your arrays first and finish with the LAMBDA. It must be the final argument.

⚠

Function not available

MAP returns a #NAME? error in older Excel versions that do not include it.

→ MAP needs Microsoft 365 or Excel for the web. On older versions, copy a normal formula down each row, or use SUMPRODUCT for element-by-element math.

Tips & notes

MAP returns one value for each input position, so the result is always the same shape as your source arrays. The LAMBDA should return a single value per call, not its own array.

Common questions

What is the difference between MAP and a normal dragged-down formula?

MAP does the same work in one formula and spills the results automatically. It resizes with your data, so you never have to drag the formula down again when rows are added.

Can MAP work with more than one column at a time?

Yes. Pass as many arrays as you need, then give the LAMBDA one parameter per array. MAP processes the values position by position across every array.

Why do I get a #VALUE! error from MAP?

The most common causes are arrays of different sizes or a LAMBDA whose parameter count does not match the number of arrays. Check that both line up.

Does MAP work in Google Sheets?

Yes. Google Sheets has its own MAP function with the same idea: MAP(array1, [array2, ...], LAMBDA). The arguments work the same way as in Excel.