How Do I Find the Cell That Contains the Maximum or Minimum Value in Excel?

Finding the maximum or minimum values in Excel is important when you’re working with a lot of data. Luckily, you can use formulas to find the cells that contain these maximum and minimum values. In this tutorial, I will show you the most effective ways to find the cell addresses that have both the maximum and minimum values in rows and columns.

Find Cell Address From a Column of Numbers:

Let’s look at a worksheet with sales values in column B, from row 4 to row 18. These values are random and can change. Please refer to the image for more details.

Formula to Find the Cell Address That Has the Maximum Value From Rows:

=CELL("address",INDEX(B4:B18,MATCH(MAX(B4:B18),B4:B18,0)))

• MAX(B4:B18) – Finds the highest (maximum) value in the range B4:B18.

• MATCH(MAX(B4:B18), B4:B18, 0) :

  • MATCH looks for the maximum value (calculated by MAX(B4:B18)) in the range B4:B18.

  • The 0 in the MATCH function means it looks for an exact match.

  • MATCH returns the relative position of that maximum value in the range.

  • For example, if the maximum value is in cell B10 (which is the 7th cell in the range B4:B18), it returns 7.

• INDEX(B4:B18, 7) – It returns a reference to the 7th cell in the range B4:B18 (which is B10).

• CELL(“address”, …) – It returns the cell address of the cell that contains the maximum value.

Copy & paste the above formula to find the cell address containing the maximum value. The formula will return the result as “$B$8“. The value $B$8 indicates the location of the maximum value in the current worksheet.

Formula to Find the Cell Address That Has the Minimum Value From Rows:

=CELL("address",INDEX(B4:B18,MATCH(MIN(B4:B18),B4:B18,0)))

• MIN(B4:B18): It finds the smallest (minimum) value in the range.

The above formula will find the cell address that contains the minimum value in the worksheets. The formula will return the result as “$B$11“. The value $B$11 indicates the location of the minimum value in the current worksheet.

Find Cell Address From a Row of Numbers:

Similarly, the sales values range from column C to column P, all in row 3. Please refer to the image below.

Formula to Find the Cell Address That Has the Maximum Value From Columns:

=CELL("address",INDEX(C3:P3,MATCH(MAX(C3:P3),C3:P3,0)))

• MAX(C3:P3) – Finds the highest (maximum) value in the range C3:P3.

• MATCH(MAX(C3:P3), C3:P3, 0) :

  • MATCH looks for the maximum value (calculated by MAX(C3:P3)) in the range C3:P3.

  • The 0 in the MATCH function means it looks for an exact match.

  • MATCH returns the relative position of that maximum value in the range.

  • For example, if the maximum value is in cell M3 (which is the 3rd cell in the range C3:P3), it returns 3.

• INDEX(C3:P3, 3) – It returns a reference to the 3rd cell in the range C3:P3 (which is M3).

• CELL(“address”, …) – It returns the cell address of the cell that contains the maximum value.

Executing the above formula will return the cell address value that contains the maximum value from the different columns. For example, the formula will return the cell address “$M$3”, which contains the maximum value.

Formula to Find the Cell Address That Has the Minimum Value From Columns:

=CELL("address",INDEX(C3:P3,MATCH(MIN(C3:P3),C3:P3,0)))

• MIN(C3:P3): It finds the smallest (minimum) value in the range.

Similarly, the formula will return the cell address that contains the minimum value from the multiple columns. For example, the formula will return the cell address “$J$3” which contains the minimum value from the columns.

Functions Used to Achieve This:

That’s it. This tutorial was originally published on How Do I Find Cell Contains Maximum or Minimum Values in Excel?