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]

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

 

[Aladin Akyurek]

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:

=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]

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)

 

[Aladin Akyurek]

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]

Sorry.

Thanks for your help. I get it now!

Much appreciated.

 

[Aladin Akyurek]

Sorry.

Thanks for your help. I get it now!

Much appreciated.

You are welcome.