Schedule

[SB lightning]

Hello

I have a list of teams playing each other with away teams in A1:A13 and home teams in B1:B13. I then have a list of players (column C) with the corresponding teams with their team listed in column D. How can I have excel automatically populate the opposing team name by referencing back to the original schedule into column E?

For example,
[TABLE="width: 500"]
<tbody>[TR]
[TD="align: center"]A
[/TD]
[TD="align: center"]B
[/TD]
[TD="align: center"]C
[/TD]
[TD="align: center"]D
[/TD]
[TD="align: center"]E
[/TD]
[/TR]
[TR]
[TD]TEAM A
[/TD]
[TD]TEAM B
[/TD]
[TD]Williams
[/TD]
[TD]TEAM D
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]TEAM C
[/TD]
[TD]TEAM D
[/TD]
[TD]Brandt
[/TD]
[TD]TEAM C
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]TEAM E
[/TD]
[TD]TEAM F
[/TD]
[TD]Jayson
[/TD]
[TD]TEAM A
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Powers
[/TD]
[TD]TEAM F
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Hoyt
[/TD]
[TD]TEAM B
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
What formula do I have to put in column E for excel to read TEAM B in column D and automatically put TEAM A into E? I don't know why this is so hard for me to explain. Let me know if you need better clarification. Thanks!

 

[estevaoba]

Try this and copy down:

[TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]=IFERROR(INDEX($A$2:$D$10,MATCH(D2,$B$2:$B$10,0),1),"")[/TD]
[/TR]
</tbody>[/TABLE]

Note that there's no match for second and third row. Is that ok?

 

[estevaoba]

That can be taken care of with this one that looks up the other way around when the first one is no match:

[TABLE="width: 64"]
<tbody>[TR]
  [TD="width: 64"]=IFERROR(IFERROR(INDEX($A$2:$D$10,MATCH(D2,$B$2:$B$10,0),1),INDEX($A$2:$D$10,MATCH(D2,$A$2:$A$10,0),2)),"")[/TD]
[/TR]
</tbody>[/TABLE]

Godspeed!

 

[SB lightning]

That can be taken care of with this one that looks up the other way around when the first one is no match:

[TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]=IFERROR(IFERROR(INDEX($A$2:$D$10,MATCH(D2,$B$2:$B$10,0),1),INDEX($A$2:$D$10,MATCH(D2,$A$2:$A$10,0),2)),"")[/TD]
[/TR]
</tbody>[/TABLE]

Godspeed!

Awesome this worked perfectly! Thank you!