Show Cell Reference

C

CJ

Hi Groupies

I would like to be able to see which cell the Max function is referring to.

For example: I have a list of values in B1 - B250. The Max function returns
the highest value in that range, but I would also like to find out which cell
contains that value. Is this possible?
 
C

CJ

Hi, I it is close, but not quite.

I created a column of data starting at 10 incrementing by 5 from D2 - D20
(10 - 100). When I use your suggestion in cell D21, it give me the answer of
$B$19

Here is my formula: =ADDRESS(MATCH(MAX(D2:D21),D2:D21,0),2)

???
 
W

willwonka

I should have said that the 2 stood for the second column which is
column B. If you are in column D, then a 4 should work.
 
W

willwonka

Also, the MATCH formula returns the row number within the range, so if
you don't start on row 1 then would need to add the amount of rows
above your range.

=ADDRESS(MATCH(MAX(D2:D20),D2:D20,0)+1,4)
 
Top