Retrieve Numeric Label for Max Value by Specific Month
 

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

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200702/1

Retrieve Numeric Label for Max Value by Specific Month
 

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

etc.
--
Gary's Student
gsnu200706


"Sam via OfficeKB.com" wrote:

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

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200702/1

Retrieve Numeric Label for Max Value by Specific Month
 

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" wrote:

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

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200702/1

Retrieve Numeric Label for Max Value by Specific Month
 

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

Gary''s Student wrote:
For Jan use:
=INDEX(A:A,MATCH(MAX(C:C),C:C,FALSE))
for Feb use:
=INDEX(A:A,MATCH(MAX(D:D),D:D,FALSE))
for Mar use:
=INDEX(A:A,MATCH(MAX(E:E),E:E,FALSE))

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200702/1

Retrieve Numeric Label for Max Value by Specific Month
 

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(B 11),MONTH(A1:H1),0))),INDEX
(A2:H5,0,MATCH(MONTH(B11),MONTH(A1:H1),0)),0))

Very much appreciated.

Cheers,
Sam

JMB wrote:
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))

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(B 11),MONTH(A1:H1),0))),INDEX
(A2:H5,0,MATCH(MONTH(B11),MONTH(A1:H1),0)),0))

where B11 contains the month (Jan) .

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200702/1

Retrieve Numeric Label for Max Value by Specific Month
 

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

"Sam via OfficeKB.com" wrote:

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(B 11),MONTH(A1:H1),0))),INDEX
(A2:H5,0,MATCH(MONTH(B11),MONTH(A1:H1),0)),0))

Very much appreciated.

Cheers,
Sam

JMB wrote:
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))

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(B 11),MONTH(A1:H1),0))),INDEX
(A2:H5,0,MATCH(MONTH(B11),MONTH(A1:H1),0)),0))

where B11 contains the month (Jan) .

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200702/1

Retrieve Numeric Label for Max Value by Specific Month
 

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))),I NDEX(C2:H5,,MONTH(B11)),0))

Biff

"Sam via OfficeKB.com" <u4102@uwe wrote in message
news:6e03af7eb1e48@uwe...
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(B 11),MONTH(A1:H1),0))),INDEX
(A2:H5,0,MATCH(MONTH(B11),MONTH(A1:H1),0)),0))

Very much appreciated.

Cheers,
Sam

JMB wrote:
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,A 1: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(B 11),MONTH(A1:H1),0))),INDEX
(A2:H5,0,MATCH(MONTH(B11),MONTH(A1:H1),0)),0))

where B11 contains the month (Jan) .

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200702/1

Retrieve Numeric Label for Max Value by Specific Month
 

Hi Biff,

Thanks for that. Very much appreciated.

Cheers,
Sam

T. Valko wrote:
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

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200702/1

Retrieve Numeric Label for Max Value by Specific Month
 

You're welcome!

Biff

"Sam via OfficeKB.com" <u4102@uwe wrote in message
news:6e0fc3261dcfb@uwe...
Hi Biff,

Thanks for that. Very much appreciated.

Cheers,
Sam

T. Valko wrote:
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

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200702/1