Array Formula Using Max Match Logic

  • Thread starter Thread starter JR573PUTT
  • Start date Start date
J

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.
 
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
 
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
 
Back
Top