Try this:
A1 = lookup_date
=MATCH(MAX(INDEX(C5:H8,MONTH(A1),)),INDEX(C5:H8,MO NTH(A1),),0)
Biff
"Sam via OfficeKB.com" <u4102@uwe wrote in message
news:6e194d2957635@uwe...
Hi All,
I would like a flexible Formula to find the Maximum value for a specific
Day (1st, 2nd, 10th etc) & Month (criteria will vary) and retrieve its
corresponding Numeric Label from
Row "4" Column "C" : "AO"
Numeric Labels = Row4 Col "C" - "AO"
Data = Row5:Row16 Col "C" - "AO" - Numeric Values
Day = Row5:Row16 Col "A" - Full Date formatted with Custom Day Format
"dd": 1
- 31
Month = Row5:Row16 Col "B" - Full Date formatted with Custom Month Format
"mmm": Jan -
Dec.
Sample Data Layout:
Row4 Day Month 1 2 3 4 5 6
Row5 1 Jan 101 102 123 143 136 128
Row6 1 Feb 120 130 103 87 143 130
Row7 1 Mar 120 90 60 200 102 88
Row8 1 Apr 170 88 79 67 141 110
Expected Results:
Looking at 1st January, the maximum value is 143 and I would expect
Numeric
Label
4 to be returned as the correct answer.
Looking at 1 April, the maximum value is 170 and I would expect Numeric
Label 1 to be returned as the correct answer.
Thanks
Sam
--
Message posted via http://www.officekb.com