Cell Address

[Guest]

I have a range ( J8:T8) which contain various values. I am using MAX(J8:T8)
to return the largest number in that range. I also need to know what the cell
address is of that value. Is there a formula I can write that will return the
address of the cell in the range with the highest value?

Thanks!

 

[JE McGimpsey]

This is one way to return the first max value in the range:

=ADDRESS(8,9+MATCH(MAX(J8:T8),J8:T8,0))

 

[Guest]

Wow! That does it! I'm quite sure that I wouldn't have gotten to this for a
long time--if ever. Thank you so much!

M. Link

 

[Don Guillett]

try this one line array formula which must be entered with ctrl+shift+enter

ADDRESS(MAX(IF(A1:C10=MAX(A1:C10),ROW(A1:C10))),MAX(IF(A1:C10=MAX(A1:C10),CO
LUMN(A1:C10))))