# 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
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

### 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
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
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
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
Sorry.

Thanks for your help. I get it now!

Much appreciated.

#### Aladin Akyurek

##### MrExcel MVP
Sorry.

Thanks for your help. I get it now!

Much appreciated.

You are welcome.

Replies
9
Views
414
Replies
1
Views
44
Replies
4
Views
84
Replies
18
Views
169
Replies
0
Views
26

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