(Part 2) - Retrieve Numeric Label for Max Value by Specific Day & Month

[Sam via OfficeKB.com]

Hi All,

I have numerous subsets of data listed by Day: 1st - 31st & Month: Jan - Dec.

Is it possible to provide a single Formula to reference a large group of data
containing some miscellaneous rows that should not be reflected in the data
returned. I would like a flexible Formula to explicitly reference specific
Day and Month to find the Maximum value for that specific Day (1st, 2nd, 10th
etc) & Month (criteria will vary) and retrieve its corresponding Numeric
Label from Row "4" Column "C" : "AO"

I may want to find the Numeric Label that has the maximum value for 1st Mar,
2nd Mar, 5 Mar; or 31 Feb, 31 Mar, 31 Dec.

Col "A" = Day, Col "B" = Month, Col "C" - "AO" = Data - Numeric Values

Col "A" Col "B" Col "C" - "AO"
01 Jan
01 Feb
01 Mar
etc
01 Dec
Misc
Misc
02 Jan
02 Feb
02 Mar
etc
02 Dec
Misc
Misc
31 Jan
31 Feb
31 Mar
etc
etc
31 Dec


Numeric Labels = Row4 Col "C" - "AO"
Data = Row5:Row438 Col "C" - "AO" - Numeric Values
Day = Row5:Row438 Col "A" - Full Date formatted with Custom Day Format "dd":
1 - 31
Month = Row5:Row438 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 01 Jan 101 102 123 143 136 128
Row6 01 Feb 120 130 103 87 143 130
Row7 01 Mar 120 90 60 200 102 88
Row8 01 Apr 170 88 79 67 141 110
etc
etc
Row16 01 Dec 185 188 179 167 141 110
Row17 Misc
Row18 Misc
Row19 02 Jan 101 102 123 143 136 128
Row20 02 Feb 120 130 103 87 143 130
Row21 02 Mar 120 90 60 200 102 88
Row22 02 Apr 170 88 79 67 141 110
etc
etc
Row31 02 Dec 100 102 182 107 111 110
Row32 Misc
Row33 Misc
Row425 31 Jan 103 142 112 114 137 118
Row426 31 Feb 120 130 103 87 143 130
Row427 31 Mar 120 90 60 200 102 88
Row428 31 Apr 170 88 79 67 141 110
etc
etc
Row436 31 Dec 128 98 145 198 102 156

Cheers,
Sam

 

[Domenic]

Assuming that A2 contains the day of interest (true date value formatted
as "dd"), and B2 contains the month of interest (true date value
formatted as "mmm"), try the following formula which needs to be
confirmed with CONTROL+SHIFT+ENTER...

=INDEX(C4:AO4,MATCH(MAX(INDEX(C5:AO436,MATCH(1,IF(DAY(A5:A436)=DAY(A2),IF
(MONTH(B5:B436)=MONTH(B2),1)),0),0)),INDEX(C5:AO436,MATCH(1,IF(DAY(A5:A43
6)=DAY(A2),IF(MONTH(B5:B436)=MONTH(B2),1)),0),0),0))

If, however, A2 contains the day of interest, such as 1, 2, 3, etc., and
B2 contains the month of interest, such as Jan, Feb, Mar, etc., try the
following instead...

=INDEX(C4:AO4,MATCH(MAX(INDEX(C5:AO436,MATCH(1,IF(DAY(A5:A436)=A2,IF(TEXT
(B5:B436,"mmm")=B2,1)),0),0)),INDEX(C5:AO436,MATCH(1,IF(DAY(A5:A436)=A2,I
F(TEXT(B5:B436,"mmm")=B2,1)),0),0),0))

Hope this helps!

 

[Sam via OfficeKB.com]

Hi Domenic,

Thank you for reply and assistance. Formula provides expected results. Great!

How do I return the Maximum value from the same set of data rather than the
Numeric Label?

Cheers,
Sam

Assuming that A2 contains the day of interest (true date value formatted
as "dd"), and B2 contains the month of interest (true date value
formatted as "mmm"), try the following formula which needs to be
confirmed with CONTROL+SHIFT+ENTER...

=INDEX(C4:AO4,MATCH(MAX(INDEX(C5:AO436,MATCH(1,IF(DAY(A5:A436)=DAY(A2),IF
(MONTH(B5:B436)=MONTH(B2),1)),0),0)),INDEX(C5:AO436,MATCH(1,IF(DAY(A5:A43
6)=DAY(A2),IF(MONTH(B5:B436)=MONTH(B2),1)),0),0),0))

This works Great!

 

[Domenic]

Hi Domenic,

Hi Sam!

Thank you for reply and assistance.

You're very welcome!

How do I return the Maximum value from the same set of data rather than the
Numeric Label?

Try...

=MAX(INDEX(C5:AO436,MATCH(1,IF(DAY(A5:A436)=DAY(A2),IF(MONTH(B5:B436)=MON
TH(B2),1)),0),0))

....confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!

 

[Sam via OfficeKB.com]

Hi Domenic,

That's Brilliant! Thank you very much.

Cheers,
Sam