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