MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Return Column Number of maximum value


Posted by JAF on June 28, 2001 2:53 AM

Hiya

I have a spreadsheet with data that needs "checking" in columns J to T (numbers 10 to 20).

I can easily identify the maximum value in these columns using =MAX(J2:T2) which returns a value of (for example) 3456.78, but I now need to identify the column number that contains that value.

I have tried messing about with the COLUMN function, but can't get it to work in conjunction with the MAX function.

Is there any way using a formula to return the column number of the highest value in a range???

NB: If the maximum value appears in more than one column, I need the formula to retun the column number of the FIRST instance.

Over to you guys (and girls!)


JAF


Posted by cpod on June 28, 2001 7:54 AM

=MATCH(MAX(J2:T2),J2:T2,0)+9