Retrieve Numeric Label for Max Value by Specific Month

[Sam via OfficeKB.com]

Hi All,

I would like a flexible Formula to find the Maximum value for a specific
Month (criteria will vary) and retrieve its corresponding Numeric Label from
Column "A".

Row1 Col "C" - "N" = Full Date formatted with Custom Month Format "mmm": Jan -
Dec.
Row2:Row31 Col "C" - "N" = Numeric Values 0 - 200
Row2:Row31 Col "A" Numeric Labels = 1 - 30


Sample Data Layout:
Row1 Labels Misc Jan Feb Mar Apr May Jun
Row2 1 0 101 102 123 143 136 128
Row3 2 0 120 130 103 87 143 130
Row4 3 0 120 90 60 200 102 88
Row5 4 0 170 88 79 67 141 110

NB. The zero (0) values should be under the column heading Misc.

Expected Results:
Looking at January, the maximum value is 170 and I would expect Numeric Label
4 to be returned as the correct answer.

Looking at February, the maximum value is 130 and I would expect Numeric
Label 2 to be returned as the correct answer.


Thanks
Sam

 

[Gary''s Student]

For Jan use:
=INDEX(A:A,MATCH(MAX(C:C),C:C,FALSE))
for Feb use:
=INDEX(A:A,MATCH(MAX(D),D,FALSE))
for Mar use:
=INDEX(A:A,MATCH(MAX(E:E),E:E,FALSE))

etc.

 

[JMB]

If your table is in A1:H5 with Labels in column A and Jan in column C, you
could try:

=INDEX(A1:A5,MATCH(MAX(C1:C5),C1:C5,0))

If you don't want to hardcode the range for the month (ie C1:C5), you could
try:

=INDEX(A1:A5,MATCH(MAX(INDEX(A1:H5,0,MATCH(B11,A1:H1,0))),INDEX(A1:H5,0,MATCH(B11,A1:H1,0)),0))

where B11 contains the month (Jan) .

 

[Sam via OfficeKB.com]

Hi Gary''s Student,

Thank you very much for your time and assistance. Your Formulae does return
the correct values.

However, I would like only the specific month's result returned to a control
sheet and have the option of changing the required month via an input cell.

I think the solution provided by JMB works a little better for my
requirements and layout.

Much appreciated.

Cheers,
Sam

 

[Sam via OfficeKB.com]

Hi JMB,

Thank you very much for your time and assistance. I've tweaked your Formula
slightly and array entered it (Ctrl+ Shift + Enter).

=INDEX(A2:A5,MATCH(MAX(INDEX(A2:H5,0,MATCH(MONTH(B11),MONTH(A1:H1),0))),INDEX
(A2:H5,0,MATCH(MONTH(B11),MONTH(A1:H1),0)),0))

Very much appreciated.

Cheers,
Sam

If your table is in A1:H5 with Labels in column A and Jan in column C, you
could try:

If you don't want to hardcode the range for the month (ie C1:C5), you could
try:

=INDEX(A1:A5,MATCH(MAX(INDEX(A1:H5,0,MATCH(B11,A1:H1,0))),INDEX(A1:H5,0,MATCH(B11,A1:H1,0)),0))

I got #N/A using Formula as it stands. However, this slight tweak & array
entered works for me.

=INDEX(A2:A5,MATCH(MAX(INDEX(A2:H5,0,MATCH(MONTH(B11),MONTH(A1:H1),0))),INDEX
(A2:H5,0,MATCH(MONTH(B11),MONTH(A1:H1),0)),0))

 

[JMB]

You're welcome. I overlooked that part about the date being a full date.

 

[T. Valko]

Since your date headers are in ascending order you can simplify that to
(normally entered, not an array):

=INDEX(A2:A5,MATCH(MAX(INDEX(C2:H5,,MONTH(B11))),INDEX(C2:H5,,MONTH(B11)),0))

Biff

 

[Sam via OfficeKB.com]

Hi Biff,

Thanks for that. Very much appreciated.

Cheers,
Sam

 

[T. Valko]

You're welcome!

Biff

Hi Biff,

Thanks for that. Very much appreciated.

Cheers,
Sam