Using Index & Match Functions to return cell values plus OFFSET ! 3

[TomYC]

I need to return the contents of a cell in a worksheet. The column number will be static (say, 1, or really, 4) while the row number will be a varying number of rows below a row number in the worksheet, and that reference row number I can obtain with the formula:
=MATCH("UNIT: TOTAL",'current ytd rank new'!$B$1:$B$2500,0)
so for example, in my curren ytd rank new worksheet, this formula returns the value of 2237. This means that the formula:
=INDEX('current ytd rank new'!$A$1:$F$2500,MATCH("UNIT: TOTAL",'current ytd rank new'!$B$1:$B$2500,0),1)
which is based on the first formula, returns me the cell reference whose column is $A and whose row is 2237.
OK, but now I want to reference a row, below (beyond) this row number, and whose contents in col. A will be certain text that I specify (eventually this text will be itself referenced from another worksheet, but for this diagnostic I can stipulate it), say "speed of admission."
So, I have a cell-based starting point, and the text-string contents of the cell to which I want to reference, probably with the OFFSET function. And finally I'll offset over to column 4 and I'll have it.
Can someone advise me about how to OFFSET from a cell reference point a certain number or rows indicated by the number of rows away from the reference cell that certain text contents can be found?
Many thanks--
T.Y.

 

[Gavona]

I think I correctly identified your data structure. And that my example works.

It should take only 10 minutes to get my solution working.

You may find that Step3 "3.Convert the text in column B,C and D to formulae" is unnecessary as today when I double checked the solution I posted (on a different PC) I found I did not need it as TextToColumns had already made the values in B11 to D34 formulae. In this situation the macro I posted would have an undesirable affect of removing all the formulae.
I hope this didn't confuse!

Once you have explored my solution it can readily be applied to your data:

Rename the datasheet to be the same as your real data sheet ("current ytd rank new", I think)
Copy the results sheet into the workbook containing your data.
Edit, Links, Change Source to change to your workbook.

On the results sheet change B2 to B7 to match your requirements.


Gavin