Complicated lookup reference in table

[BigTenBoy15]

Hello! Need some help with a dynamic lookup reference, likely using some sort of Index/Match combination as I'm trying to lookup a player name in a table based on his position and team name. For example, if I use the table below, the row reference in the first Match can only be B2:B16 or D2:D16, but I want it to be one or the other depending on the name in row one (Team 1 or Team 2). So if I'm trying to lookup the player associated with RB2 on Team 2, then I'd have to change the row reference in the Match formula. Is there any way to do this with a formula and without a macro?

	A	B	C	D
1​	Team 1		Team 2
2​	Dak Prescott	QB1	Carson Wentz	QB1
3​	Jameis Winston	QB2	Ezekiel Elliott	RB1
4​	James Conner	RB1	Aaron Jones	RB2
5​	Joe Mixon	RB2	Devin Singletary	RB3
6​	Sony Michel	RB3	Miles Sanders	RB4
7​	Matt Breida	RB4	Chris Thompson	RB5
8​	Jaylen Samuels	RB5	Tyrell Williams	WR1
9​	Amari Cooper	WR1	Sterling Shepard	WR2
10​	Deebo Samuel	WR2	Kenny Stills	WR3
11​	Zach Pascal	WR3	Breshad Perriman	WR4
12​	Robby Anderson	WR4	Mark Andrews	TE1
13​	Diontae Johnson	WR5	Darren Waller	TE2
14​	Jacob Hollister	TE1	Buffalo Bills	DEF1
15​	Tennessee Titans	DEF1	Green Bay Packers	DEF2
16​	Kansas City Chiefs	DEF2	Chicago Bears	DEF3

 

[Eric W]

Try:

Book1
	A	B	C	D	E	F	G	H	I
1	Team 1		Team 2				Team	Position	Player
2	Dak Prescott	QB1	Carson Wentz	QB1			Team 2	RB4	Miles Sanders
3	Jameis Winston	QB2	Ezekiel Elliott	RB1
4	James Conner	RB1	Aaron Jones	RB2
5	Joe Mixon	RB2	Devin Singletary	RB3
6	Sony Michel	RB3	Miles Sanders	RB4
7	Matt Breida	RB4	Chris Thompson	RB5
8	Jaylen Samuels	RB5	Tyrell Williams	WR1
9	Amari Cooper	WR1	Sterling Shepard	WR2
10	Deebo Samuel	WR2	Kenny Stills	WR3
11	Zach Pascal	WR3	Breshad Perriman	WR4
12	Robby Anderson	WR4	Mark Andrews	TE1
13	Diontae Johnson	WR5	Darren Waller	TE2
14	Jacob Hollister	TE1	Buffalo Bills	DEF1
15	Tennessee Titans	DEF1	Green Bay Packers	DEF2
16	Kansas City Chiefs	DEF2	Chicago Bears	DEF3
Sheet5

Cell Formulas
Range	Formula
I2	I2	=INDEX(A2:C16,MATCH(H2,INDEX(B2:D16,0,MATCH(G2,A1:C1,0)),0),MATCH(G2,A1:C1,0))

 

[BigTenBoy15]

That worked! Thanks so much!

 

[Eric W]

Happy to help! ?