View Single Post
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
T. Valko T. Valko is offline
external usenet poster
 
Posts: 15,768
Default Retrieve Numeric Label for Max Value by Specific Day & Month

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