Excel quick tip: Return the cell address of the maximum value in a column

This relatively simple Excel formula returns the address of a cell containing the maximum value in a row of data:-

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

Where the A:A parts of the formula denote the column which contains your data.

Note: Make sure that your formula is entered in a cell in a different column to the data you are searching in otherwise Excel will throw a circular reference error at you.

Note 2: If the maximum value appears twice in your data, the formula will return the cell address of the first instance of the value in the data, from top to bottom.

Note 3:  Replace ‘MAX’ with ‘MIN’ to find the address of the lowest value in your data.

One comment

  • December 15, 2014 - 5:37 pm | Permalink

    Question: I have a column (A1:A10) of peoples names and a column (B1:B10) of numbers. I want to put into cell C1 a function that will locate the cell in B1:B10 that contains the smallest number and then display the person’s name from A1:A10 that is located just to the left of that number. Any help would be appreciated.

  • Leave a Reply

    Your email address will not be published. Required fields are marked *