Help with index match multiple criteria and multiple columns

[ks-man]

I'm putting together a baseball lineup and position matrix for my kid. Players name is in column A and positions are in columns B-G with each position listed in the subsequent columns.

I'm trying to do a lookup where I have a baseball diamond and if I enter an inning number it will then show the player name (from column A) that is playing the position for that inning. I can easily return the column number by inning (=match(H4, B2:G2,0) however I can't figure out how to use the index match portion to search for the row of the returned column to then give back the name in column A.

Thanks for the help!

 

[Fluff]

How about

+Fluff New.xlsm
	A	B	C	D	E	F	G	H	I
1	Name	A	B	C	D	E	F
2	Tom Pearce			xyz
3	Bill Brewer			klm
4	Jan Stewer			opq				C
5	Peter Gurney			mno				abc	Dan'l Whiddon
6	Peter Davy			def
7	Dan'l Whiddon			abc
8	Harry Hawke			tuv
9	Tom Cobley			ijk
10
11
Main

Cell Formulas
Range	Formula
I5	I5	=INDEX(A2:A9,MATCH(H5,INDEX(B2:G9,,MATCH(H4,B1:G1,0)),0))

 

[ks-man]

That works. Thanks so much!

 

[Fluff]

You're welcome & thanks for the feedback.