(Part 3) - Retrieve Numeric Label for Max Value by Criteria across Single Row

[Sam via OfficeKB.com]

Hi All,

I very much appreciate all the help I have received with these formulae.

I would appreciate further assistance in adapting the following formulae to
Return across a Single
Row Multiple Numeric Labels that share the same Maximum value (duplicate
maximums).


Formula (1) Array Entered - Retrieve Numeric Label for Max Value by Specific
Month
Col "A" (A2:A31) = Numeric Labels
Col "C" - "N" (C1:N1) = Full Date formatted with Custom Month Format "mmm":
Jan - Dec
Col "C" - "N" (C2:N31) = Data Numeric Values

=INDEX(GrowthA'!$A$2:$A$31,MATCH(MAX(INDEX('GrowthA'!$C$2:$N$31,0,MATCH(MONTH
($D2),MONTH(GrowthA'!$C$1:$N$1),0))),INDEX('GrowthA'!$C$2:$N$31,0,MATCH(MONTH
($D2),MONTH(GrowthA'!$C$1:$N$1),0)),0))

Formula (2) Array Entered - Retrieve Numeric Label for Max Value by Specific
Day & Month
Col "A" (A2:A438) = Full Date formatted with Custom Day Format "dd": 1 - 31

Col "B" (B5:B438) = Full Date formatted with Custom Month Format "mmm": Jan -
Dec
Col "C" - "AO" (C4:AO4) = Numeric Labels
Col "C" - "AO" (C5:AO438) = Data Numeric Values

=INDEX('GrowthB'!$C$4:$AO$4,MATCH(MAX(INDEX('GrowthB'!$C$5:$AO$438,MATCH(1,IF
(DAY('GrowthB'!$A$5:$A$438)=DAY($D6),IF(MONTH('GrowthB'!$B$5:$B$438)=MONTH(
$D6),1)),0),0)),INDEX('GrowthB'!$C$5:$AO$438,MATCH(1,IF(DAY('GrowthB'!$A$5:$A
$438)=DAY($D6),IF(MONTH('GrowthB'!$B$5:$B$438)=MONTH($D6),1)),0),0),0))


Thanks
Sam

 

[Domenic]

Try the following formulas, which need to be confirmed with
CONTROL+SHIFT+ENTER...

For the first one...

E2:

=MAX(INDEX(GrowthA!$C$2:$N$31,0,MATCH(MONTH($D2),MONTH(GrowthA!$C$1:$N$1)
,0)))

F2:

=COUNTIF(INDEX(GrowthA!$C$2:$N$31,0,MATCH(MONTH($D2),MONTH(GrowthA!$C$1:$
N$1),0)),$E2)

G2, copied across:

=IF(COLUMNS($G2:G2)<=$F2,INDEX(GrowthA!$A$2:$A$31,SMALL(IF(INDEX(GrowthA!
$C$2:$N$31,0,MATCH(MONTH($D2),MONTH(GrowthA!$C$1:$N$1),0))=$E2,ROW(Growth
A!$A$2:$A$31)-ROW(GrowthA!$A$2)+1),COLUMNS($G2:G2))),"")

For the second one...

E6:

=MAX(INDEX(GrowthB!$C$5:$AO$438,MATCH(1,IF(DAY(GrowthB!$A$5:$A$438)=DAY($
D6),IF(MONTH(GrowthB!$B$5:$B$438)=MONTH($D6),1)),0),0))

F6:

=COUNTIF(INDEX(GrowthB!$C$5:$AO$438,MATCH(1,IF(DAY(GrowthB!$A$5:$A$438)=D
AY($D6),IF(MONTH(GrowthB!$B$5:$B$438)=MONTH($D6),1)),0),0),$E6)

G6, copied across:

=IF(COLUMNS($G6:G6)<=$F6,INDEX(GrowthB!$C$4:$AO$4,SMALL(IF(INDEX(GrowthB!
$C$5:$AO$438,MATCH(1,(DAY(GrowthB!$A$5:$A$438)=DAY($D6))*(MONTH(GrowthB!$
B$5:$B$438)=MONTH($D6)),0),0)=$E6,COLUMN(GrowthB!$C$4:$AO$4)-COLUMN(Growt
hB!$C$4)+1),COLUMNS($G6:G6))),"")

Hope this helps!

 

[Sam via OfficeKB.com]

Hi Domenic,

Thank you very much. That's absolutely Brilliant!!

Cheers,
Sam