This function maps each value in the given arrays to a new value by application of a LAMBDA function to each value.
Sample Usage
MAP(A1:A5, LAMBDA(cell, cell*2)): MAP function with range as input.
MAP(A1:A5, B1:B5, LAMBDA(cell1, cell2, MAX(cell1, cell2))): MAP function with multiple ranges as input.
MAP(UNIQUE(A1:A10), LAMBDA(number, number + 1)): MAP function with array as input.
Syntax
MAP(array1, [array2, ...], LAMBDA)
array1: An array or range to be mapped.array2, …: [OPTIONAL] Additional arrays or ranges to be mapped.LAMBDA: ALAMBDAfunction that’s mapped to each value in the given arrays to obtain a new mapped value.- Syntax:
LAMBDA(name1, [name2, …], formula_expression) - Requirements:
- The
LAMBDAmust have exactly 1nameargument for each array passed, along with aformula_expressionwhich uses those names. WhenLAMBDAis applied, the names resolve to the current values being mapped in the passed arrays.
- The
- Syntax:
Notes
-
The passed
LAMBDAfunction should accept exactly as manynamearguments as the number of input arrays given toMAP, otherwise an#N/Aerror is returned. These arguments correspond to the values in the input arrays which are being mapped to a new value. -
Values in the input arrays should map to a single value. Array results for mapped values aren’t supported.
- A
named functioncan be passed for theLAMBDAparameter and behaves like aLAMBDAin this case. Learn more about named functions.- There should be exactly as many argument placeholders defined for it as the number of input arrays passed to
MAP. - The
named functionshouldn't be followed by parenthesis.
- There should be exactly as many argument placeholders defined for it as the number of input arrays passed to
Examples
Simple doubling operation with MAP
Example data:
|
A |
B |
C |
D |
|
|---|---|---|---|---|
|
1 |
1 |
2 |
||
|
2 |
3 |
4 |
||
|
3 |
2 |
4 |
||
|
4 |
6 |
8 |
Example: Input this formula in C3: =MAP(A1:B2, LAMBDA(cell, cell*2))
Map a comma separated values to hyphenated SKU codes
Example Data:
|
A |
B |
|
|---|---|---|
|
1 |
Jeans,Black,XL |
Jeans-Black-XL |
|
2 |
Shorts,Brown,S |
Shorts-Brown-S |
|
3 |
Tshirt,Red,L |
Tshirt-Red-L |
|
4 |
Skirt,Pink,M |
Skirt-Pink-M |
Example: Input this formula in B1: =MAP(A1:A4, LAMBDA(item, JOIN("-", SPLIT(item, ","))))
Map multiple input ranges to the max value in each data set
Example Data:
|
A |
B |
C |
D |
E |
F |
G |
H |
|
|---|---|---|---|---|---|---|---|---|
|
1 |
38.9 |
17.8 |
42 |
20.2 |
38.6 |
21.2 |
||
|
2 |
39.2 |
19.6 |
37.8 |
17.1 |
34.6 |
21.2 |
||
|
3 |
34.1 |
18.1 |
41.1 |
17.6 |
36.6 |
17.8 |
Example: =MAP(A1:B3, D1:E3, G1:H3, LAMBDA(valA, valB, valC, MAX(valA, valB, valC)))
Result:
|
A |
B |
|
|---|---|---|
|
1 |
42 |
21.2 |
|
2 |
39.2 |
21.2 |
|
3 |
41.1 |
18.1 |
Use a named function as a LAMBDA to count cells which have numbers in them
Example Data:
|
A |
B |
C |
|
|---|---|---|---|
|
1 |
13 Going On 30 |
2 Fast 2 Furious |
12 Angry Men |
|
2 |
Eternal Sunshine of the Spotless Mind |
Friday the 13th |
No Country for Old Men |
Named function: CONTAINS_NUMBER is a named function which checks if the given string value contains any number.
Formula definition: =ARRAYFORMULA(OR(ISNUMBER(SPLIT(cell, " ")))), where cell is an argument placeholder defined for CONTAINS_NUMBER.
Example: =COUNTIF(MAP(A1:C2, CONTAINS_NUMBER), true)
Result:
|
3 |
Common Errors
The dimensions of the input arrays don’t matchIf the dimensions of the input arrays don’t match, this error occurs:
“Array arguments to MAP are of different size.”
Example: =MAP(C1:C4, D1:D2, LAMBDA(x, x+1))
In this example, array C1:C4 doesn’t match the size of array D1:D2.
If the passed LAMBDA doesn’t have exactly as many name arguments as the number of input arrays given to MAP, this error occurs:
“Wrong number of arguments to LAMBDA. Expected 3 arguments, but got 2 arguments.
Example: =MAP(C1:C4, D1:D4, LAMBDA(cell, cell+1))
In this example, LAMBDA was given only 1 name argument cell, even though we passed 2 arrays to MAP.
If the last parameter of MAP wasn’t a LAMBDA, this error occurs:
“Argument must be a LAMBDA.”
Example: =MAP(C1:C3, 3)
If the 1 or more name arguments aren’t valid, this error occurs:
“Argument 1 of function LAMBDA is not a valid name.”
Example: =MAP(C1:C3, LAMBDA(C1, C1+1))
In this example, C1 is an invalid name since it clashes with a range.
If the application of LAMBDA on the input array(s) maps each value to multiple values or another array, this error occurs:
“Single value expected. Nested array results are not supported.”
Example: =MAP(E1, LAMBDA(word, SPLIT(word, " ")))
In this example, we try to map the text in the cell to an array of words.
Related functions
- LAMBDA function: This function lets you create and return a custom function with a set of
namesand aformula_expressionthat uses them. - REDUCE function: This function reduces an array to an accumulated result.
- BYROW function: This function groups an array by rows.
- BYCOL function: This function groups an array by columns.
- SCAN function: This function scans an array and produces intermediate values.
- MAKEARRAY function: This function creates a calculated array of specified dimensions.
- Create & use named functions: This function lets users create and store custom functions, similar to
LAMBDA.
