Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports

# Thread: Stumped with MATCH & INDEX

1. I agreed to these rules
Join Date
14th July 2012
Posts
5

## Stumped with MATCH & INDEX

I'm stumped. I have 4 columns of data. I'm needing to match a name from column A in column C, look at the corresponding team number in column B and display the other team members on the same team as the person I'm searching for from column A. Here's a sample of what I'm working with:
 Lookup Team # Team Member Teammates Bob 1 Jim Alan, Julia Jim 1 Alan Jim, Julia Sue 1 Julia Jim, Alan Doris 2 Bob Christine, Ralph Ralph 2 Christine Bob, Ralph Julia 2 Ralph Bob, Christine Christine 3 Ryan Doris, Sue Ryan 3 Doris Ryan, Sue Alan 3 Sue Ryan, Doris

Any help would be greatly appreciated!

Excel Video Tutorials / Excel Dashboards Reports

2. OzMVP
Join Date
25th November 2006
Posts
1,182

## Re: Stumped with MATCH & INDEX

Hello,

Do you mean the output you are looking for is Column D with the "other" Team Mates ...

If it is the case, you will need an array formula to extract all team members and then use substitute() to remove the one already listed ...

Will prepare a quick sample for you ...

Edit : see attached example
Last edited by Carim; 1 Week Ago at 17:25. Reason: added workbook

Excel Video Tutorials / Excel Dashboards Reports

3. I agreed to these rules
Join Date
14th July 2012
Posts
5

## Re: Stumped with MATCH & INDEX

Your sample makes sense. If I don't have column C 'Teammates', and only have columns A&B (Lookup and Team #), is there a way to find the teammates of each individual?

 Lookup Team # Bob 1 Jim 1 Sue 1 Doris 2 Ralph 2 Julia 2 Christine 3 Ryan 3 Alan 3

Excel Video Tutorials / Excel Dashboards Reports

4. OzMVP
Join Date
25th November 2006
Posts
1,182

## Re: Stumped with MATCH & INDEX

Hello,

Attached is your revised workbook ... with your new requirement ...

Hope this will help

Excel Video Tutorials / Excel Dashboards Reports

5. OzMVP
Join Date
25th November 2006
Posts
1,182

## Re: Stumped with MATCH & INDEX

Hello,

Hope you had a chance to review the latest modification ...

Do not hesitate to share your comments ...

Excel Video Tutorials / Excel Dashboards Reports

6. I agreed to these rules
Join Date
14th July 2012
Posts
5

## Re: Stumped with MATCH & INDEX

My apologies for not replying sooner.

Woo hoo! That last sample worked well.

Finally, I have 2 Bobs and 2 Jims in the group (though both don't always participate). I've added last names and I have it somewhat working. Is there an easy way to incorporate the ampersand instead of the comma between names in the 'Team Mates' column (column G) within the Team Mates worksheet so that the team mates would display 'Jim Woodford & Sue Sosna' or 'Bob Fields & Julia Griffin'? I can get the 'Team Mates' column (column G) to display 'Jim Woodford Sue Sosna' (spaces in-between all the names), or I can display 'Jim,Woodford,Sue,Sosna' (commas between all the names) but I haven't figured out how to put an ampersand between the names.

I've looked at nesting a CONCAT formula within the SUBSTITUTE formula, but it's not going well.

I attached the workbook with the name updates. If you can help with this last hurdle, it would make my week -- no, my month!

Thank you so much for all of your help!

Test TeamMates.xlsm

Excel Video Tutorials / Excel Dashboards Reports

7. OzMVP
Join Date
25th November 2006
Posts
1,182

## Re: Stumped with MATCH & INDEX

Hope attached workbook is in line with your expectations ...

Excel Video Tutorials / Excel Dashboards Reports

8. I agreed to these rules
Join Date
14th July 2012
Posts
5

## Re: Stumped with MATCH & INDEX

Any reason when I apply the formulas across the entire list of people (not just the sample you've worked on), the CONCAT formula doesn't output what I'm looking for? I copied over the VB module from the sample and the 'Team Mates' column (Column G in the Team Mates worksheet) outputs properly based on the value in the 'Whole Team' column (Column B in the Team Mates worksheet).

If Column B would just output properly, I'd have it figured out. Hmmm. So close...

Test TeamMates V3.xlsm

Excel Video Tutorials / Excel Dashboards Reports

9. OzMVP
Join Date
25th November 2006
Posts
1,182

## Re: Stumped with MATCH & INDEX

Hello again,

Attached is your file with a tiny correction ...

Hope this will help

Excel Video Tutorials / Excel Dashboards Reports

10. I agreed to these rules
Join Date
14th July 2012
Posts
5

## Re: Stumped with MATCH & INDEX

Problem(s) solved! Thank you so much! The last fix did the trick.

Excel Video Tutorials / Excel Dashboards Reports

##### Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•
porno