How to Use MAP in Google Sheets: Comprehensive Guide

MAP applies a function to every item of one or more arrays and returns the results as a new array of the same shape. It is the array-aware way to say "do this to each value", especially when a function doesn't work on whole ranges by itself.

Syntax

=MAP(array1, [array2, ...], lambda)

array1: The range or array to process.
array2, ... [Optional]: More arrays of the same size, processed in parallel.
lambda: A LAMBDA whose parameters match the arrays, one per array, followed by the calculation. This must be the last argument.

The LAMBDA runs once per item. For two arrays, it receives the matching pair of items each time. The output has the same dimensions as the input arrays.

Basic examples

=MAP(A2:A10, LAMBDA(x, x * 2))

Doubles each value in A2:A10.

=MAP(A2:A10, B2:B10, LAMBDA(price, qty, price * qty))

Multiplies each price by the quantity next to it. Both arrays must be the same size.

=MAP(A2:A10, LAMBDA(name, UPPER(TRIM(name))))

Cleans and capitalizes every name.

Handling blanks

A range like A2:A includes the empty cells down to the bottom, and MAP would process every one of them. Guard the blanks inside the LAMBDA:

=MAP(A2:A, LAMBDA(x, IF(x = "", "", x * 2)))

Or filter them out first:

=MAP(FILTER(A2:A, A2:A <> ""), LAMBDA(x, x * 2))

Why MAP, when there is ARRAYFORMULA?

ARRAYFORMULA works for functions that accept arrays, but many functions collapse a range into a single result or don't expand properly, such as TEXTJOIN, SPLIT, INDEX or functions with complex logic. MAP runs the whole LAMBDA for each item, so anything you can calculate for one value can be applied to a whole list:

=MAP(A2:A10, LAMBDA(text, COUNTA(SPLIT(text, " "))))

Counts the words in each cell. COUNTA(SPLIT(...)) would collapse everything with ARRAYFORMULA, but works item by item with MAP.

Practical patterns

Multi-step cleaning per item

=MAP(A2:A100, LAMBDA(x, LET(y, TRIM(x), IF(y = "", "", PROPER(y)))))

Conditional labels

=MAP(B2:B100, LAMBDA(score, IFS(score >= 90, "A", score >= 80, "B", score >= 70, "C", TRUE, "F")))

Lookups with fallback

=MAP(A2:A100, LAMBDA(key, IFERROR(VLOOKUP(key, Prices!A:B, 2, FALSE), "n/a")))

Combine two columns

=MAP(A2:A100, B2:B100, LAMBDA(first, last, first & " " & last))

Per-cell calculations that return text

=MAP(A2:A100, LAMBDA(d, TEXT(d, "mmm yyyy")))

Map over a 2D range

With a multi-column range, MAP goes cell by cell and returns the same shape:

=MAP(A2:C10, LAMBDA(x, IF(x = "", "", x * 1.1)))

To process whole rows or columns at once, use BYROW or BYCOL instead, which hand an entire row or column to the LAMBDA:

=BYROW(A2:C10, LAMBDA(row, SUM(row)))

Use a Named function

If you saved a LAMBDA as a Named function, pass it by name:

=MAP(A2:A100, CLEANTEXT)

Common errors

#N/A or "Wrong number of arguments". The LAMBDA has a different number of parameters than the arrays you provided. One parameter per array.
Arrays of different sizes. When you pass several arrays, they must have the same dimensions.
Results in unwanted blank rows. The range is open-ended, such as A2:A. Guard blanks or use a bounded range.
#REF!: "Array result was not expanded because it would overwrite data". The output needs room to spill. Clear the cells below.
The lambda isn't last. MAP's last argument must be the LAMBDA.
Slow on huge ranges. The function runs once per cell. Keep ranges bounded.

MAP vs. ARRAYFORMULA vs. BYROW vs. REDUCE

MAP: apply a function to each item, return a same-shaped array.
ARRAYFORMULA: expand an ordinary formula over a range, and it is shorter when the function supports arrays.
BYROW / BYCOL: apply a function to each whole row or column and return one result per row or column.
REDUCE / SCAN: carry a running value through the list, and return the final or every intermediate value.

Frequently asked questions

Does MAP change the original data? No. It returns a new array.

Can MAP return text and numbers? Yes, the LAMBDA can return anything.

Can the LAMBDA return more than one value per item? No. It should return a single value per item, so the output stays the same shape as the input.

Can I use MAP on a single value? Yes, but it is pointless; use the formula directly.

Related Functions

LAMBDA: Create custom functions.
LET: Name values inside a formula.
ARRAYFORMULA: Apply a formula to a whole range.
FILTER: Return the rows that meet a condition.
SPLIT: Divide text at a delimiter.

MAP