Use Excel MAP and LAMBDA Function to Group Values

Excel’s MAP function is a powerful tool that allows you to apply custom operations to each element in an array. In this blog, I’ll show you how to group values based on unique values using MAP, LAMBDA, TEXTJOIN, and FILTER functions. This is useful if you want to collect related values and display them as a single comma-separated string.

Watch the Tutorial

Scenario

Imagine you have a list of customer orders. You want to group the order IDs for each customer into a single cell.

You want to group the values for each unique name, like this:

How to Achieve This Using MAP, TEXTJOIN, and FILTER

Step 1: Generate Unique Names

Use the UNIQUE function to extract the list of unique names:

=UNIQUE(A2:A8)

This will spill the unique names starting from a selected cell (say E4), and the result will be:

Step 2: Use FILTER Extract the values

Enter the below formula in the cell F4 and you can make it absolute reference by adding the $

=FILTER($B$2:$B$8,$A$2:$A$8=E4)

You may have observed that we haven’t set E4 as an absolute reference. To leverage Dynamic Arrays, we should add the # symbol to E4, making it E4#. However, doing this now will result in a Value error, which is where the MAP and LAMBDA functions become useful. We will address this shortly; for now, let’s proceed with building the solution.

Step 3: Use TEXTJOIN group the values

We will wrap the formula with the TEXTJOIN function you are free you use your own delimiter. Here we will be using comma “,”.

=TEXTJOIN(",",TRUE,FILTER($B$2:$B$8,$A$2:$A$8=E4))

By filling down the formula, you will obtain the result; however, to enhance its dynamism, we should utilize Dynamic Arrays.

Let us add the # key and see result.

As mentioned above, an error will occur because we need to iterate the value from the array stored in the cell E4#.

This is the point we need to bring more power using the MAP and LAMBDA Function.

Step 4: Use MAP and Lambda to Group Values Dynamically

Now, use the MAP function to iterate over the unique names and TEXTJOIN to concatenate the matching values.

Here’s the formula:

=MAP(E4#, LAMBDA(criteria, TEXTJOIN(", ", TRUE, FILTER(B2:B9, A2:A9=criteria))))

The MAP function applies an operation to each unique name in the E4# spill range, while the LAMBDA function defines a custom function where ‘criteria’ refers to each unique name from that range. ‘Criteria’ is simply a variable name defined within LAMBDA.

Final Thoughts

This method enables the grouping and displaying of related values in Excel by utilizing the MAP, LAMBDA, TEXTJOIN, and FILTER functions. It offers a dynamic approach that excels with spill ranges and can be effortlessly applied to extensive datasets without manual intervention.

Give it a try in Excel 365 or Excel 2021 or Google Sheets and make your data analysis more efficient!