Google Sheets Function: MAP

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

Usage:

=MAP(array, LAMBDA)

or

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

Example of use

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

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 ";"):

=SPLIT(A2,";")

The SPLIT function successfully returns the divided data.

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

=MAP(A2:A21

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

=MAP(A2:A21,LAMBDA(data

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

=MAP(A2:A21,LAMBDA(data,SPLIT(data,";")))

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

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:

=SORT(MAP(A2:A21,LAMBDA(data,SPLIT(data,";"))))

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

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