Get the max value of a column given the current date (this is hard!!!)

[Shadow_Otixz]

It's nice that it was able to recognize its reason code and its ma
value for the date located at B2, but what if the date at a1 wa
changed, let's say the value of
a1 is 11/2/04?

How can it locate the column of the current date located at a1? an
find its max value and its reason code?

the value of a1 is the current date.

I hope that you can still help me with this one, thanks!

Grac

 

[JulieD]

Hi Grace

to get the max value for the date in A1 the formula is
=MAX(OFFSET(A2,1,MATCH(A1,B2:F2,0),5,1))

and to get the reason the formula is
=INDEX(A2:F7,MATCH(MAX(OFFSET(A2,1,MATCH(A1,B2:F2,0),5,1)),OFFSET(A2,1,MATCH(A1,B2:F2,0),5,1),0)+1,1)

(i set my workbook up with dates from B2:F2, reasons from A3:A7 and # of
occurances for each of these) and the date to return information for in A1

Hope this helps
Cheers
JulieD