Retrieve Numeric Label for Max Value by Specific Day & Month

[Sam via OfficeKB.com]

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

 

[T. Valko]

Try this:

A1 = lookup_date

=MATCH(MAX(INDEX(C5:H8,MONTH(A1),)),INDEX(C5:H8,MONTH(A1),),0)

Biff

 

[Sam via OfficeKB.com]

Hi Biff,

Thanks for reply and assistance.

Unfortunately, I'm not getting the expected result.
In your Formula where you referenced MONTH twice, should one be referenced
DAY?
For the Numeric Label to be returned from Row "4", Column "C" - "AO" does
this range need to be explicitly referenced?

Further help appreciated.

Cheers,
Sam

 

[T. Valko]

In your Formula where you referenced MONTH twice, should one be referenced

DAY?

No. MATCH returns the relative position.

Since your DAY headers are sorted ascending *and* your month headers are
also sorted ascending we only need to find the relative position of the
month.

For the Numeric Label to be returned from Row "4", Column "C" - "AO" does
this range need to be explicitly referenced?

Again, no, for the same reason as stated above. We only need to find the
relative position of the DAY and, since they're sorted ascending we don't
actually need to reference that physical range.

Here's a sample file based on your posted data:

Sam.xls 14kb

http://cjoint.com/?cuu20MDM1I

Biff

 

[Sam via OfficeKB.com]

Hi Biff,

Thank you very much for explanation and sample file.

Your Formula as is does provide the required result. Great!

A1 = lookup_date

=MATCH(MAX(INDEX(C5:H8,MONTH(A1),)),INDEX(C5:H8,MONTH(A1),),0)

I tried to reference the DAY separately which returned the incorrect result:
=MATCH(MAX(INDEX(C5:H8,MONTH(A1),)),INDEX(C5:H8,DAY(A1),),0)

Cheers
Sam

No. MATCH returns the relative position.

Since your DAY headers are sorted ascending *and* your month headers are
also sorted ascending we only need to find the relative position of the
month.

Again, no, for the same reason as stated above. We only need to find the
relative position of the DAY and, since they're sorted ascending we don't
actually need to reference that physical range.

 

[T. Valko]

You're welcome. Thanks for the feedback!

Biff

Hi Biff,

Thank you very much for explanation and sample file.

Your Formula as is does provide the required result. Great!

A1 = lookup_date

=MATCH(MAX(INDEX(C5:H8,MONTH(A1),)),INDEX(C5:H8,MONTH(A1),),0)

I tried to reference the DAY separately which returned the incorrect
result:
=MATCH(MAX(INDEX(C5:H8,MONTH(A1),)),INDEX(C5:H8,DAY(A1),),0)

Cheers
Sam