Display cell ref for MAX formula

[bookman3]

Hi,

In simple terms if I have a formula of:
=MAX(A1:A4) and the maximun value is in cell A3,
I would like to be able to display the cell reference of A3 rather than the
actual maximum value.
Is that possible?

 

[Sheeloo]

Try
="A" & MATCH(MAX(A1:A4),A1:A4)

 

[bookman3]

This works fine.
However, my actual formula is more complicated.
A simplified version of it is:
=Max(A1:A4,B1:B4)
How can I apply that soluion to this formula

 

[Sheeloo]

So you want to find a maximum in more that one columns and then get the cell
address?

 

[bookman3]

Yes I do.
There are actually 5 columns that are not next to each other with row
numbers from 7 to 70

Hope you can help

 

[muddan madhu]

try this ( source exceltip)

this is an arrray formula use (ctrl + shift + enter)

=ADDRESS(SUM(IF(A1:F4=MAX(A1:F4),ROW(A1:F4))),SUM(IF(A1:F4=MAX(A1:F4),COLUMN(A1:F4))))

 

[bookman3]

Thanks

This works fine, I just need to adapt it so that the max reference refers to
non adjacent rows but it should work with a series of if statements

 

[JMB]

Note that if there is more than one cell that equals the maximum value, it
will not work properly.

If that is a potential issue, you could try this (to search by row
=ADDRESS(MIN(IF(A1:H4=K1,ROW(A1:H4))),MIN(IF(INDEX(A1:H4,MIN(IF(A1:H4=K1,ROW(A1:H4))),0)=K1,COLUMN(A1:H4))))

or thi
=ADDRESS(MIN(IF(INDEX(A1:H4,MIN(IF(A1:H4=K1,COLUMN(A1:H4))),0)=K1,COLUMN(A1:H4))),MIN(IF(A1:H4=K1,COLUMN(A1:H4))))

to search by column. they both appeared to work okay, but you'd have to
include the entire range that encompasses your data - I have no other ideas
how to accomplish this on a non-contiguous range.