Retrieve the Cell Address and Value of the Maximum Number in Excel
This article explains how to find the cell address (e.g., A3) and value of the maximum number within a specified range in Excel. We will demonstrate how to set up separate cells to display the address and the value, along with the formulas to achieve this.
Steps
- Prepare a cell (e.g., B1) to display the address of the maximum value and another cell (e.g., C1) to display the value itself.
- Specify the range containing the values (e.g., A2:A10) and enter the respective formulas.
Formula Explanation
To retrieve the cell address and value of the maximum number in a range, we use a combination of INDEX, MATCH, and MAX functions.
Formula to Retrieve the Maximum Value
Enter the following formula in cell C1 to get the maximum value:
=MAX(A2:A10)
This formula returns the highest value within the specified range (A2:A10).
Formula to Retrieve the Cell Address of the Maximum Value
Enter the following formula in cell B1 to get the address of the maximum value:
=ADDRESS(MATCH(MAX(A2:A10), A2:A10, 0) + ROW(A2) – 1, COLUMN(A2), 4)
Formula Details:
- MAX(A2:A10): Finds the maximum value in the specified range.
- MATCH(MAX(A2:A10), A2:A10, 0): Finds the relative position of the maximum value within the range.
- ROW(A2) – 1: Adjusts the row number based on the range’s starting position.
- COLUMN(A2): Returns the column number of the range.
- ADDRESS(…, 4): Converts the row and column numbers into a relative cell address (e.g., A5).
Example
In the table below, column A contains numerical data. Cell B2 displays the address of the maximum value, and cell C2 displays the value itself.
| A | B | C | |
|---|---|---|---|
| 1 | Values | Max Value Address | Max Value |
| 2 | 5 | =ADDRESS(MATCH(MAX(A2:A10), A2:A10, 0) + ROW(A2) – 1, COLUMN(A2), 4) | =MAX(A2:A10) |
| 3 | 12 | ||
| 4 | 8 | ||
| 5 | 25 | ||
| 6 | 18 | ||
| 7 | 7 | ||
| 8 | 13 | ||
| 9 | 20 | ||
| 10 | 2 |
Results
- Cell Address (B2): A5
- Maximum Value (C2): 25
Summary
To find the cell address and value of the maximum number in a range in Excel, use a combination of ADDRESS, MATCH, and MAX functions. This method enables you to easily identify where the maximum value is located within your data.