Using VLOOKUP return as row number in function

Hi, this is quite a random request, but I am wondering about the best syntax for using a VLOOKUP return as the row number in a CORREL function. I want to create rolling correlations from today's date. I have a VLOOKUP function that will return the row number corresponding to the chosen day's date.

I now need to use that returned value in the CORREL function. That is, I would like it to look something like:

=CORREL($E$VLOOKUP(today-90,AD5:AE3143, 2):$E$VLOOKUP(today,AD5:AE3143, 2),$E$VLOOKUP(today-90,AD5:AE3143, 2):$E$VLOOKUP(today,AD5:AE3143, 2))

Does this make sense? When I enter this, I am told that I have an error. Is there a better way to nest this vlookup?

Thanks so much for your help and please let me know if I can clarify anything!

Michael

Try:

Please Login or Register  to view this content.

is "today" a named cell representing today's date? If so, you know there is a built in function: Today() that you can replace that with?

Or better (non-volatile):

Please Login or Register  to view this content.