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

  • Thread starter Thread starter Shadow_Otixz
  • Start date Start date
S

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
 
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
 
Back
Top