INDEX(MATCH) dynamic column range?

I will use a brief scenario to try to explain the formula I am looking for:

The formula will always start in cell AR4. What I want the formula to do is search all of row 2 for the word "Composite". When it finds the first instance of "Composite", I would like the formula to return the value in that column that corresponds with the row the formula is placed in.

For example, say the word "Composite" is in Column C. Then AR4 should equal the value in C4. AR5 should equal the value in C5, etc...

Is this possible with a formula? If not, VBA is fine as well.

Thank you for the help!

Originally Posted by Tony Valko

Maybe something like this...

Data Range

	A	B	C	D	E	F
1
2	Doobie	Scooby	Composite	Cramps	------	Composite
3
4	Doobie1	Scooby1	Composite1	Cramps1		Composite1
5	Doobie2	Scooby2	Composite2	Cramps2		Composite2
6	Doobie3	Scooby3	Composite3	Cramps3		Composite3
7	Doobie4	Scooby4	Composite4	Cramps4		Composite4
8	Doobie5	Scooby5	Composite5	Cramps5		Composite5
9

This formula entered in F4:

=INDEX(A$4:D$8,ROWS(F$4:F4),MATCH(F$2,A$2:D$2,0))

Copy down as needed.

Works perfectly! Thank you so much!