Trying to do index-match but I have multiple teams for one name... how can I return all those teams for that name?

doge_3

Board Regular
Joined
Apr 11, 2014
Messages
72
I've got names in one column, and teams in another.

I'm mapping names to teams, but some ppl are on multiple teams.

For example, John is part of both the Project Management team and Systems Engineering team.

How can I write my formula so that for John, my result shows all the teams he belongs to? Note: some people belong to as many as 9 teams...

Not sure how I can do this..

Please let me know
 

Some videos you may like

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,192
I've got names in one column, and teams in another.

I'm mapping names to teams, but some ppl are on multiple teams.

For example, John is part of both the Project Management team and Systems Engineering team.

How can I write my formula so that for John, my result shows all the teams he belongs to? Note: some people belong to as many as 9 teams...

Not sure how I can do this..

Please let me know

A2:A100 houses the names, B2:B100 the teams the names are associated with.

D1: John

D2: Team List

D3, control+shift+enter, not just enter, and copy down:
Rich (BB code):
=IFERROR(INDEX($B$2:$B$100,SMALL(IF($A$2:$A$100=$D$1,
  ROW($B$2:$B$100)-ROW($B$2)+1),ROWS($D$3:D3))),"")
 

doge_3

Board Regular
Joined
Apr 11, 2014
Messages
72
Thanks.

Is there any way to get it in just one cell?

BTW I have two workbooks and I'm trying to link up names to teams from one to another.

In my other worksheet I have just names, and I need to put their associated teams in there but into one cell. How to do that?

Pls let me know!

For example, WB 2 has from A1:A100 just names with B1:B100 just teams.

I want to show All teams in one cell only in B.

Also here's the format in the original (A,B)

(John, Engineering)
(John, Project Management)
(John, General)
 
Last edited:

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,192
Thanks.

Is there any way to get it in just one cell?

BTW I have two workbooks and I'm trying to link up names to teams from one to another.

In my other worksheet I have just names, and I need to put their associated teams in there but into one cell. How to do that?

Pls let me know!

For example, WB 2 has from A1:A100 just names with B1:B100 just teams.

I want to show All teams in one cell only in B.

Also here's the format in the original (A,B)

(John, Engineering)
(John, Project Management)
(John, General)

What is the point of posting a different story the first time around?

That asked, try to adapt from the link below:
http://www.mrexcel.com/forum/excel-questions/805851-lookup-value-combine-texts.html#post3940201
 

doge_3

Board Regular
Joined
Apr 11, 2014
Messages
72
Sorry.

Thanks for your help. I get it now!

Much appreciated.
 

Watch MrExcel Video

Forum statistics

Threads
1,108,491
Messages
5,523,255
Members
409,506
Latest member
reneekeane

This Week's Hot Topics

Top