Array Formula Using Max Match Logic

[JR573PUTT]

I have the following spreadsheet:


r1 mar mar mar apr apr
r2 apr 20 25 30 40 50
r3 mar 50 20 25 30 15


r1 is cel a1, in (cel b2) formula resides, I want in cel b2 the
corresponding month in row 1 with the highest value, in r2 the highest
value is 50(cel g2), so I want the name in cel g1 to appear in cel b2.

In r3 the highest value is 50, so I want the formula to return the name
mar which is in cel c1.

What is the best formula to use, thanks.

 

[Bernie Deitrick]

JR573PUTT,

=INDEX($B$1:$G$1,MATCH(MAX(B2:G2),B2:G2,FALSE))

Change the G's to your actual last column.

Copy down for one row.

HTH,
Bernie
MS Excel MVP

 

[Ken Johnson]

Hi JR573PUTT,
another formula is

=INDIRECT(ADDRESS(1,MATCH(MAX(C2:G2),C2:G2,FALSE)+COLUMN()))

and I think Bernie's formula should be

=INDEX($C$1:$G$1,MATCH(MAX(C2:G2),C2:G2,FALSE))

Bernie's is better since it involves fewer function calls

Ken Johnson

 

[JR573PUTT]

Thank you both formulas work fine, thanks again.

 

[Ken Johnson]

Hi JR573PUTT,
You're welcome, thanks for the feedback.
Ken Johnson