INDEX MATCH when the target column is not known

[tonkerthomas]

I've tried to find another thread that deals with this topic, without success, so here goes:

I have a very large block of data: thousands of rows by hundreds of columns. I need to identify a known value in that block and return the value two cells to the right of it. I can use INDEX to ascertain which row of my data the value will be in, but it could be in any column. How do I locate the value, and how do I offset my return?

Example:

	Colhead1	Colhead2	Colhead3	Colhead4	Colhead5	Colhead6
TeamA	Dave	Nigel	Sarah	Ryan	Sally	Gav
TeamB	Eric	Clive	Agnes	Bert	Dora	Rita
TeamC	Katie	Mary	Jake	Will	Kiki	Harry

<tbody>
</tbody>

I'm looking to find Clive, say, and want to return Bert. I know that Clive is in team B but I don't know where in the columns his name appears so I can't INDEX MATCH with Colhead2. I'm sure there's a way of doing this but I don't know how and an hour of Googling hasn't helped me. So, can you guys?

Thanks, as always, in advance for your help

Jeff

 

[Fluff]

How about

+Fluff New.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1		Colhead1	Colhead2	Colhead3	Colhead4	Colhead5	Colhead6
2	TeamA	Dave	Nigel	Sarah	Ryan	Sally	Gav			teamB
3	TeamB	Eric	Clive	Agnes	Bert	Dora	Rita			Clive	Bert
4	TeamC	Katie	Mary	Jake	Will	Kiki	Harry
Lookup

Cell Formulas
Range	Formula
K3	K3	=INDEX(B2:G4,MATCH(J2,A2:A4,0),MATCH(J3,INDEX(B2:G4,MATCH(J2,A2:A4,0),0),0)+2)

 

[tonkerthomas]

Aha! An INDEX MATCH INDEX MATCH! That's logical, but sadly, too complicated for this bear of very little brain to figure out by himself. Thanks a million, Fluff - your time is very much appreciated.

Jeff

 

[Fluff]

You're welcome & thanks for the feedback

 

[donblue]

try this =INDEX($A:$A,(ROW()-1)*3+COLUMN(A1)).
I used it to make print out available in 3 columns similar to Words print 'number of columns' excel2013

 

[Fluff]

Did you realise that this thread is over a year old?
Also you formula does not do what the OP was asking for.