Hi - Dave here.
Happy Friday!
Wow, there were a lot of people who emailed me with solutions to last week's challenge! I've compiled some of the most interesting formula solutions and added them now to the "answer" section on the challenge page here:
Formula challenge - list names by teams
If you still want to try this challenge, stop reading and click the link above to download the worksheet and give it a try.
Listing names by teams in a single cell
The core of the challenge is how to find and retrieve all the names associated with a team, and return them in one cell as a comma-separated text list. One way to approach this problem is to use the FILTER function with the TEXTJOIN function, as you can see in the worksheet below:
[
Download the workbook and read the full explanation]
This is a great example of how the FILTER function's ability to retrieve more than one value can be useful in surprising ways. Many people don't think of FILTER as a lookup function, but that's what it really is. However, to build the entire table in E5:F8 (challenge #2), you don't need to use FILTER at all. Read all details at the link above, then download the workbook to try it out yourself.
Note: this example requires Excel 365.
Excel formulas
We maintain a list of over 1000 working formulas
here.
If you need more structure, we also offer
video training.
Have a great weekend!
Dave
The Exceljet newsletter is free and sent weekly on Fridays.You can sign up on our home page.