How Do I Find Cell Contains Maximum or Minimum Values in Excel?

Quick & Simple Formula to Find Cell Address Contains both Maximum & Minimum values from the Columns & Rows in Excel.

Narendhiran Vijayakumar 1 minute agoLast Updated: June 2, 2025

0 0 3 minutes read

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 having 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 returns 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 having 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 contains the minimum values in the worksheets. The formula will returns the result as “$B$11“. The value $B$11 indicates the location of the minimum value in the current worksheet.

Finding Cell Address contains Both Maximum and Minimum Values from the Rows.

Find Cell Address from a Row of Numbers:

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

Formula to Find the Cell Address having 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 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 having 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 returns the cell address 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.

Finding Cell Address contains Both Maximum and Minimum Values from the Column.

Functions Used to Achieve this:

Used Functions	Functions Used for
CELL	Returns information about the formatting, location, or contents of a cell.
INDEX	Returns the value of an element in a table or array, selected by row and column number.
MATCH	Searches for a specified item in a range of cells and returns the relative position of that item.
MIN	Returns the smallest number in a set of values.
MAX	Returns the largest number in a set of values.

That’s it.

–Narendhiran Vijayakumar.

Feel free to comment us below, if you have any queries about the above topic and find more interesting excel tutorials on our homepage: Excel24x7.com.