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!