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,

A	B	C	D	E
TEAM A	TEAM B	Williams	TEAM D
TEAM C	TEAM D	Brandt	TEAM C
TEAM E	TEAM F	Jayson	TEAM A
		Powers	TEAM F
		Hoyt	TEAM B

<tbody>
</tbody>

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!