Finding the maximum value in a column in Excel

This example will explain how to find the address of the cell with the maximum value in a column.

1. First, we use the function MAX (MAX) to find the maximum value in a column А.

   =MAX(A:A)

   =МАКС(A:A)

2. Now let’s use the function MATCH (MATCH) to find the row number with the maximum value.

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

   =ПОИСКПОЗ(МАКС(A:A);A:A;0)

   

   Explanation: Function MATCH (MATCH) is reduced to =SEARCH(12;A:A;0) or =7. Those. returns the position of the maximum value in a column A. Set the third argument to 0to return an exact match.

3. Finally, we use the function ADDRESS (ADDRESS) to get the cell address.

   =ADRESS(MATCH(MAX(A:A),A:A,0),1)

   =АДРЕС(ПОИСКПОЗ(МАКС(A:A);A:A;0);1)

   

   Explanation: Function ADDRESS (ADDRESS) is reduced to =ADDRESS(7;1) or =$A$7. The first argument specifies the line number. The second argument specifies the column number.