Finding the location of MAX value in column

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I know this is a very simple question but I have found the MAX value in a
column using the usual formuls, the only problem is I don't know it's
location and I need to relate back to another value on the same row. The
column contains nearly 3000 readings so I don't fancy trawling through them
all tring to find it!
Can anyone please help asap
Thanks a million
 
Hi Milly

If you know the column this will do

=ADDRESS(MATCH(MAX(A1:A15),A1:A15),1)

It find the max in column A. If you wanted to find the max in column C then
change the ",1)" in the formula to ",C) and change the range to suit.

Regards
Peter
 
Hi Milly

Try
=ADDRESS(MATCH(MAX(A:A),A:A,0),COLUMN(A1))
would return $A$10 for example
If you copy across, then A:A would change to B:B and COLUMN(B1) and so
on.
In other words, you need to alter both parameters depending upon which
column you are searching.

Add a ,4 at the end if you wish to change the formula from Absolute to
relative

=ADDRESS(MATCH(MAX(A:A),A:A,0),COLUMN(A1),4)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Max Length in a row 1
finding the cell adjacent to the max value in a row with formula 4
Return MAX value from column B and Name from column A 2
Automatically highlight max and min values in a column in Excel 2
find location max value in column 3
Locating the max value AND performing a fn on it only 2
Finding max value in column A where value in B = 4
MAX() of a range of negative values? 7

Back
Top