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:

Using FILTER to look up more than one value

[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.

 

Exceljet Logo
Exceljet
P.O. Box 4804
Salt Lake City, UT 84110

Copyright © 2025 Exceljet, All rights reserved.
You received this email because you are subscribed to our newsletter.
To unsubscribe, click the link below.