Google Sheets Function: MAP

The MAP function applies a LAMBDA function to each value of the entered table.

Prerequisite: understand the LAMBDA function.


=MAP(array, LAMBDA)


=MAP(array1, array2, ..., LAMBDA)

Example of use

The objective here will be to split data into 3 columns:

google sheets divide into columns map

If you are not sure, you can start by writing the formula (without MAP and LAMBDA) for a single data point to ensure it works correctly.

In this case, the SPLIT function will be used to divide the data (whose 3 parts are separated by ";"):

google sheets divide into columns split function map

The SPLIT function successfully returns the divided data.

Now enter the MAP function followed by the table containing the data to be processed:


Then enter the LAMBDA function and choose a variable name (for example, "data"):


And finally, add your SPLIT function and replace A2 with the variable "data":


The MAP function will then traverse the entered table (A2:A21) and apply the split formula to each value in the table:

google sheets functions map lambda split

Of course, you could have also copied the first SPLIT function down for the other cells if that was the final result you wanted to achieve, and it would have been simpler.

But to go further, this would have limited you because the MAP function returns an array (like many other Google Sheets functions), and this array can be used with other functions.

To better understand, imagine that you now want to alphabetically sort the generated data, simply enter the formula with MAP into a SORT function:

google sheets functions sort map lambda split

You could even go further and use the QUERY function to get the data for companies that end in "LLP":

=QUERY(SORT(MAP(A2:A21,LAMBDA(data,SPLIT(data,";")))),"SELECT * WHERE Col3 LIKE '%LLP'")
google sheets functions sort map lambda split query

And just for the sake of example, know that you can apply the MAP function again if necessary... In this case, now that only the companies with "LLP" are left, let's remove the "LLP" information by applying a new MAP function.

The array to enter in the MAP function will be the one returned by the QUERY function, followed by a new LAMBDA function that will remove " LLP" from the data using SUBSTITUTE:

=MAP(QUERY(SORT(MAP(A2:A21,LAMBDA(data,SPLIT(data,";")))),"SELECT * WHERE Col3 LIKE '%LLP'"),LAMBDA(text,SUBSTITUTE(text," LLP","")))

google sheets functions sort map lambda split query substitute png

If needed, you can copy the Google Sheets document (or view the document) with these examples.