MAP function

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: A LAMBDA function that’s mapped to each value in the given arrays to obtain a new mapped value.
  • Syntax: LAMBDA(name1, [name2, …], formula_expression)
  • Requirements:
    • The LAMBDA must have exactly 1 name argument for each array passed, along with a formula_expression which uses those names. When LAMBDA is applied, the names resolve to the current values being mapped in the passed arrays.

Notes

• The passed LAMBDA function should accept exactly as many name arguments as the number of input arrays given to MAP, otherwise an #N/A error 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 function can be passed for the LAMBDA parameter and behaves like a LAMBDA in 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 function shouldn't be followed by parenthesis.

Examples

Simple doubling operation with MAP

Example data:

Example: Input this formula in C3: =MAP(A1:B2, LAMBDA(cell, cell*2))

Map a comma separated values to hyphenated SKU codes

Example Data:

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:

Example: =MAP(A1:B3, D1:E3, G1:H3, LAMBDA(valA, valB, valC, MAX(valA, valB, valC)))

Result:

Use a named function as a LAMBDA to count cells which have numbers in them

Make a Copy

Example Data:

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:

Common Errors

The dimensions of the input arrays don’t match The passed LAMBDA doesn’t have exactly as many name arguments as the number of input arrays  The last parameter of MAP wasn’t a LAMBDA The LAMBDA passed to MAP was incorrect The application of LAMBDA on the input array(s) maps to multiple values or another array

Related functions

• LAMBDA function: This function lets you create and return a custom function with a set of names and a formula_expression that 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.