Reference to adjacent cell of maximum

[corus]

I have a column of data from which I can simply find the maximum value using the MAX function. How can I find out where that maximum value occurs in the column so I can reference that cell or more importantly the cell adjacent to that maximum (in the same row) some where later?

 

[ab123456]

You need to use the match function.

Try something like MATCH(MAX(A1:A9),A1:A9,0) This will return the position in the array of the max value in the range a1:a9. The 0 at the end of the function ensures that you only get a response if an exact match is found.

Hope that helps

 

[corus]

Thanks ab,
I've managed to do this now by using the MATCH, ADDRESS, and INDIRECT functions to return the value of a cell that is in the same row as the maximum of another column.

 

[joerd]

Don't forget the INDEX and OFFSET functions here:
as in INDEX(C1:C9, MATCH(MAX(A1:A9),A1:A9,0))

Cheers,
Joerd

Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.