Thread: Offset??

I need to lookup a date in a row header and when the date is found, search down the column and find the largest numeric value and return the value one cell above.

Not sure where to start with this one.

Thanks for any help.

-ep

Hi,

As a first step ... formula should return max ...
=MAX(INDIRECT(ADDRESS(1,MATCH("yourdate",A1:M1,0))):INDIRECT(ADDRESS(10,MATCH("yourdate",A1:M1,0))))


HTH
Carim

Offset would work, the obvious assumption is that the date is unique

if the dates in the header are in a2:h2, the date is in b1, and the data in a3:h1001

its a bit horrid and theremay be a better way but

=OFFSET(A2,MATCH(MAX(OFFSET(A3,0,MATCH(B1,A2:H2,0)-1,1000,1)),OFFSET(A3,0,MATCH(B1,A2:H2,0)-1,1000,1),0)-1,MATCH(B1,A2:H2,0)-1)


Regards

Dav

Both formulas are returning an error. Am I doing something wrong? I've set the ranges to be identical to the posted formulas.

-ep

It works for me, see attached

Regards

Dav

Thanks Dav.

I was able to get it working, but I didn't realize that I probably need to use LARGE instead of MAX as I will need to find the second largest value, the third largest value, etc..

Thanks again.

=OFFSET(B2,MATCH(LARGE(OFFSET(B3,0,MATCH(B1,B2:AF2,0)-1,1000,1),1),OFFSET(B2,0,MATCH(B1,B2:AF2,0)-1,1000,1),0)-2,MATCH(B1,B2:AF2,0)-1)

Thanks for the help.