How to find the cell address of the maximum or minimum value in Excel

Sometimes, knowing the maximum or minimum value in a dataset is not enough—you may also need to identify the exact location of that value, whether it's for referencing, highlighting, or reporting purposes. Excel offers formula-based methods as well as advanced tools like Kutools for Excel to help you quickly find and extract the cell address of the highest or lowest value in a range.

• Extract Address from a Column of Numbers
• Extract Address from a Row of Numbers

Find the Cell Address of the Max or Min Value Using Formulas

If you’re working with a column or a row of data, Excel formulas can help you return the cell address of the largest or smallest number using INDEX, MATCH, and CELL functions combined.

Extract Address from a Column of Numbers

Let's say your data is in cells A2:A15, to extract the cell address of the max or min value, you can use the following formulas:

Select a blank cell, enter one of the following formulas according to your needs, and then press "Enter".

To find the cell address of the max value:

=CELL("address",INDEX(A2:A15,MATCH(MAX(A2:A15),A2:A15,0)))

To find the cell address of the min value:

=CELL("address",INDEX(A2:A15,MATCH(MIN(A2:A15),A2:A15,0)))

These formulas will return something like $A$6, indicating the location of the max or min value. Here I apply the first formula to extract the cell address of the largest value in the column:

Extract Address from a Row of Numbers

If your data is in a row (e.g., A1:J1), use similar formulas to extract the cell address of the max or min value:

Select a blank cell, enter one of the following formulas into it and press "Enter".

Max value in a row:

=CELL("address",INDEX(A1:J1,MATCH(MAX(A1:J1),A1:J1,0)))

Min value in a row:

=CELL("address",INDEX(A1:J1,MATCH(MIN(A1:J1),A1:J1,0)))

The cell address of the largest value has been extracted as follows:

Find and locate address of cell with max or min value with Kutools for Excel

If you prefer a visual and interactive approach, Kutools for Excel offers a feature called Select Cells with Max & Min Value, which helps you directly select and locate the desired cells without writing any formulas.

After installing Kutools for Excel, select "Kutools" > "Select" > "Select Cells with Max & Min Value" to open the "Select Cells with Max & Min Value" dialog box. Then configure as follows.

1. Select the range that contains the values you want to analyze.
2. In the "Look in" section, choose whether to evaluate "values", "formulas" or both.
3. In the "Go to" section, select either "Maximum value" or "Minimum value".
4. In the "Base" section, choose how you want to evaluate the data within the range:
   • "Cell" – Finds the overall max or min from the entire selection (default).
   • "Row" – Finds the max or min within each row separately.
   • "Column" – Finds the max or min within each column separately.
5. In the "Select" section, select the one you need:
   • "First cell only": Will select the first found result.
   • "All cells": Will highlight all cells that match the max or min value.
6. Click "OK".

   

All matching cells will be selected instantly in your worksheet.

Related articles