How to Return the Cell Reference of the Maximum Value in Excel
Question details
The user needs to find the exact cell address containing the highest numerical value in a specified range, rather than just returning the maximum number itself.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Analyzing a dataset where locating the exact position of the peak value is required for referencing or auditing purposes.
- Observed behavior
- The standard MAX function only returns the value, so the user requires a formula combination to retrieve the cell reference (e.g., $A$2) or the corresponding data from an adjacent column.
Ensure that your target data range does not contain error values (such as #N/A or #VALUE!), as these will cause the MAX function to return an error instead of the highest number.
Get the Cell Address Using ADDRESS, XMATCH, and MAX
This method combines three built-in functions to pinpoint the exact cell reference of the highest number in a single-column range.
The MAX function finds the highest number, XMATCH locates its relative row position within the range, and ADDRESS converts that row and column number into an actual cell reference string.
Click on an empty cell where you want the resulting cell address to be displayed.
Type the formula =ADDRESS(XMATCH(MAX(A1:A4),A1:A4),1) into the formula bar. Adjust 'A1:A4' to match your actual data range. The '1' at the end specifies column A; change this number to match your target column (e.g., 2 for column B).
Press the Enter key. The cell will now display the absolute address (such as $A$2) of the maximum value.
Return an Adjacent Value Using XLOOKUP
If your primary goal is to retrieve related data from another column on the same row as the maximum value, use XLOOKUP to fetch the data directly without needing the cell address.
Easily Find Max Value References with WPS Spreadsheet
WPS Spreadsheet offers full support for advanced lookup formulas like XMATCH, XLOOKUP, and ADDRESS, allowing you to quickly locate maximum values and their exact cell references with zero hassle.
- 1. Open your workbook: Launch WPS Spreadsheet and open the document containing your dataset.
- 2. Select the target cell: Click on the cell where you want the reference or lookup result to appear.
- 3. Input the formula: Type your combined ADDRESS and XMATCH formula into the formula bar.
- 4. Get your result instantly: Press Enter to instantly locate your maximum value's exact cell reference.
Frequently Asked Questions
What happens if there are multiple identical maximum values in my range?
When using the XMATCH or MATCH function, the formula will evaluate from top to bottom and return the cell reference of the first matching maximum value it encounters in your specified range.
Can I use MATCH instead of XMATCH if I have an older version of Excel?
Yes, if your software does not support XMATCH, you can use the traditional MATCH function. The formula would look like this: =ADDRESS(MATCH(MAX(A1:A4), A1:A4, 0), 1). The '0' specifies an exact match.
How do I find the cell reference for the minimum value instead?
You can achieve this by simply replacing the MAX function with the MIN function in your formula. The updated formula will be: =ADDRESS(XMATCH(MIN(A1:A4), A1:A4), 1).
