Applies ToExcel for Microsoft 365 Excel for Microsoft 365 for Mac Excel for the web

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.

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)))

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

LAMBDA function

REDUCE function

SCAN function

MAKEARRAY function

BYCOL function

BYROW function

ISOMITTED function

Need more help?

Want more options?

Explore subscription benefits, browse training courses, learn how to secure your device, and more.

Communities help you ask and answer questions, give feedback, and hear from experts with rich knowledge.