Returns an array formed by mapping each value in the array(s) to a new value by applying a LAMBDA to create a new value.

Feature available to Office Insiders only This feature is available to Office Insiders only right now.

Syntax

=MAP (array1, lambda_or_array<#>)

The MAP function syntax has the following arguments and parameters:

  • array1      An array to be mapped.

  • lambda_or_array<#>      A LAMBDA which must be the last argument and which must have either a parameter for each array passed, or another array to be mapped.

Errors

Providing an invalid LAMBDA function or an incorrect number of parameters returns a #VALUE! error called "Incorrect Parameters".

Examples

Example 1: Square numbers above a certain value

Enter the sample data into cells A1:C2, and then copy the formula into cell D4:

=MAP(A1:C2, LAMBDA(a, IF(a>4,a*a,+a)))

Note      Calculating the last LAMBDA parameter as +a coerces it to an integer and prevents a #CALC! error.

First MAP function example1

Example 2: Find values from two columns in one row that are both TRUE

Create a table named, "TableA" with two columns named "Col1" and "Col2" starting in cell D1. Add a column named "BothTrue" in cell G1. Copy the formula into cell G2:

=MAP(TableA[Col1],TableA[Col2],LAMBDA(a,b,AND(a,b)))

Second MAP function example

Example 3: Find values from three columns in one row that meet certain criteria

Create a range of the sample data with two columns named "Size" and "Color" in cells D1:E11. Add two column headers named "Valid Size" and "Valid Color" in cells F1 and G1 respectively. Copy the formula into cell F2:

=FILTER(D2:E11,MAP(D2:D11,E2:E11,LAMBDA(s,c,AND(s="Large",c="Red"))))

Third MAP function example3

See Also

REDUCE function

SCAN function

MAKEARRAY function

BYCOL function

BYROW function

ISOMITTED function

Need more help?

Expand your skills
Explore Training
Get new features first
Join Microsoft Insiders

Was this information helpful?

What affected your experience?

Any additional feedback? (Optional)

Thank you for your feedback!

×