Find header values for a maximum in a range

0
votes

Example Data Set

I am trying to write a formula or a VBA to find

  1. The column header (located in row1)
  2. The corresponding time (located in colum1)

for the Max value in the Range C1022:D1352 from the attached excel sheet.

I have managed to extract column header value by first manually finding the maximum value in each column in the range from "C" to "D" in row 1444 and then using the formula 

=INDEX($C$1:$E$1,0,MATCH(MAX($C$1444:$E$1444),$C$1444:$E$1444,0))

Now I am trying to find corresponding time at which the maximum value occurred from column1 using the formula 

=INDEX($A1022:$A1352,MATCH(MAX(C1022:C1352),C1022:C1352,0),0)

but I have to manually update the formula for 20 sheets every day with Column Name (eg. "C") every time as the maximum could be found in either column "C" or "D".

Is there an easy way to find the maximum in a table range then get the value of the header and the value from the first column in the same row as the maximum value.

1
excelvbaexcel-formula
Will the values always be unique, can the max number happen more than once in the data range? - Scott Craner
Not sure if it is possible to attach the actual excel file here. - khalid

1 Answers

1
votes

To find the Column Header(no helper cells needed):

=INDEX($1:$1,AGGREGATE(15,7,COLUMN(C1022:D1352)/(C1022:D1352=MAX(C1022:D1352)),1))

To find the row time:

=INDEX($A:$A,AGGREGATE(15,7,ROW(C1022:D1352)/(C1022:D1352=MAX(C1022:D1352)),1))