List what Team a Person is in

[Cerebrus]

I have a spreadsheet, with two workbooks,

One has a list of every employee, the other has those same names in three separate columns each column headed with a Team Name.

I was the first sheet with the list to have a column with the team they are in next to them, and for this to update as I update the second sheet.

Is this possible?

 

[alansidman]

Show us a sample spreadsheet with 8-10 records and a mocked up solution. Do not post a picture as we cannot manipulate data in a picture. Use XL2BB.

 

[awoohaw]

what version of Excel are you using? this may be very easy in 365.

 

[awoohaw]

This is for 3 teams and up to 28 players per team. Copy the formula in B3 down as necessary:
The formula in A3 requires 365.

Book1
	A	B	C	D	E	F	G
1	Sheet1				Sheet2
2	Player	team			team1	team2	team3
3	Player7	team1			Player7	Player3	Player1
4	Player3	team2			Player2	Player4	Player15
5	Player1	team3			Player8	Player9	Player11
6	Player2	team1			Player14	Player6	Player10
7	Player4	team2			Player5	Player12
8	Player15	team3				Player13
9	Player8	team1
10	Player9	team2
11	Player11	team3
12	Player14	team1
13	Player6	team2
14	Player10	team3
15	Player5	team1
16	Player12	team2
17	Player13	team2
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
Cerebrus

Cell Formulas
Range	Formula
A3:A17	A3	=FILTER(TOCOL(E3:G30),LEN(TOCOL(E3:G30))>0)
B3:B17	B3	=INDEX( $E$2:$G$2, IF(ISNUMBER(MATCH(A3,$E$3:$E$30,0)),1, IF(ISNUMBER(MATCH(A3,$F$3:$F$30,0)),2,3)) )
Dynamic array formulas.

 

[Peter_SSs]

Another variation.

23 05 28.xlsm
	A	B	C	D	E	F	G
1	Sheet1				Sheet2
2	Player	team			team1	team2	team3
3	Player7	team1			Player7	Player3	Player1
4	Player3	team2			Player2	Player4	Player15
5	Player1	team3			Player8	Player9	Player11
6	Player2	team1			Player14	Player6	Player10
7	Player4	team2			Player5	Player12
8	Player15	team3				Player13
9	Player8	team1
10	Player9	team2
11	Player11	team3
12	Player14	team1
13	Player6	team2
14	Player10	team3
15	Player5	team1
16	Player12	team2
17	Player13	team2
Team

Cell Formulas
Range	Formula
A3:A17	A3	=LET(c,TOCOL(E3:G30),FILTER(c,c<>""))
B3:B17	B3	=CONCAT(IF(E$3:G$30=A3,E$2:G$2,""))
Dynamic array formulas.