Summary

In the worksheet shown, the goal is to list the names associated with each team, based on the data in columns B and C. The result should be a list of team members separated by commas. The solution should be dynamic. If the data changes, the result should update accordingly.

This challenge assumes you are using Excel 2021 or later. 

Details 

The goal is to find all names for a given team and return them in a single cell as a comma‑separated list. This is basically a lookup problem, but the twist is that we want to return multiple matches for each team, and we want everything in one cell. A formula like this is useful in many scenarios, including:

  • List products by category.
  • List classes by student.
  • List the staff by project.

What makes the problem unique is that we want to locate multiple matches (i.e., all members of a given team), but we the result to be delivered to a single cell formatted as a clean, readable list (i.e., “Jake, Tracy, Juan”) instead of a block names in different cells. 

For convenience, the challenge worksheet contains two named ranges: name (B5:B16) and team (C5:C16). This makes the formulas easier to read and write.

Challenge #1 - List names for each team

Assuming that we already have a list of teams in column E, what formula in column F, when copied down, will return a list of names for each team as shown below?

Formula Challenge #1 - list names for each team

Challenge #2 - List teams and names with one formula

Assuming that we don't already have a list of names in column E, what formula entered in cell E5 will return a table that includes both Teams and Names in one step?

Formula Challenge #2 - list teams and names with one formula

Thanks for participating in the challenge! I always learn something new when I run these challenges; I hope you do, too. These are the formulas I came up with before the challenge: 

=TEXTJOIN(", ",1,FILTER(name,team=E5)) // challenge #1

For challenge #2, the key insight is that you can use the GROUPBY function: 

=GROUPBY(team,name,LAMBDA(a,TEXTJOIN(", ",1,a)),,0) // challenge #2

Quite a few people suggested shorter formulas based on the ARRAYTOTEXT function: 

=ARRAYTOTEXT(FILTER(name,team=E5)) // challenge #1
=GROUPBY(team,name,ARRAYTOTEXT,,0) // challenge #2

This is a pretty cool way to make these formulas more compact, and it's nice to see the seldom-seen ARRAYTOTEXT function make an appearance! One thing to remember with the ARRAYTOTEXT function is that its delimiters are based on regional settings, which vary by user. For example, if settings are for French/France, you will see semicolons (;) instead of commas. The other potential issue is that ARRAYTOTEXT has no control over empty values. TEXTJOIN is a better option if you need to control delimiters or ignore empty values. That said, ARRAYTOTEXT is a nice way to simplify the formulas. See this page for a more detailed explanation and a worked spreadsheet you can download and try out yourself.

Other formulas

Below are some other formulas I like, starting with a roll-your-own formula for Challenge #2 by Jeet: 

=HSTACK(UNIQUE(team),BYROW(UNIQUE(team),LAMBDA(t,TEXTJOIN(", ",1,FILTER(name,team=t,"")))))

Matt Hanchett suggested several interesting formulas. This one below shows how you would use the LET function to build out the formula above to use variables: 

=LET(
uTeam,UNIQUE(team),
listArr,BYROW(uTeam,LAMBDA(t,TEXTJOIN(", ",TRUE,FILTER(name,team=t,"")))),
HSTACK(uTeam,listArr)
)

This formula is an alternative form of the formula above, but shows how you can name the lambda formula and apply it with MAP (or BYROW) "eta lambda"-style: 

=LET(
uTeam,UNIQUE(team),
listArrFn,LAMBDA(t,TEXTJOIN(", ",TRUE,FILTER(name,team=t))),
HSTACK(uTeam, MAP(uTeam,listArrFn))
)

Patrick H. also suggested several unique approaches, including this formula for Challenge #1: 

=REPLACE(CONCAT(IF(team=E5,", "&name,"")),1,2,"")

Finally, Kevin Weaver suggested this nice formula for Challenge #2 

=GROUPBY(C5:.C50,B5:.B50,ARRAYTOTEXT,0,0)

Note the clever use of the new "trim ref" operators, which make the ranges dynamic.