Reference to adjacent cell of maximum
Reference to adjacent cell of maximum
(OP)
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?





RE: Reference to adjacent cell of maximum
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
RE: Reference to adjacent cell of maximum
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.
RE: Reference to adjacent cell of maximum
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.