Find Max Value and Corresponding Cell in Excel (5 Quick Methods)

Finding the max value and corresponding cell means looking for the maximum value(s) from a dataset and returning the cell location of the value.
In this tutorial, you will learn how to find the max value and corresponding cell in Excel.
Look at the following image. Here we have an employee dataset with their ID, name, joining year, and salary. We figured out the employee name with the highest salary. And also we got the cell location of those values.

Finding the max value and corresponding cells can be done by using Excel formulas and VBA code. Some Excel formulas will return the first max value with the corresponding cell from a dataset. Some other Excel formulas can return all the corresponding cells with the maximum value. We have also shown how to determine the max value and corresponding cell based on criteria.

1. Using Excel Formula to Find First Max Value and Corresponding Cell

By Combining Excel INDEX and MATCH Functions with other Excel Functions such as MAX, LARGE, AGGREGATE, and SUBTOTAL functions, we will find the first maximum value and corresponding cell in a single dataset.

Here we are considering the below sample dataset containing Employee ID, Employee Name, Joining Year, and Salary. There are Jack Alfred and Alisha Stokes in the C13 and C14 cell who get the maximum salary of $35,000. However, the INDEX-MATCH formula returns only Jack Alfred since it is the first value in the dataset.

We can determine the maximum salary by applying the MAX function. We will use INDEX-MATCH formula along with the MAX function to extract the name of the employee who gets the maximum salary. Later, the CELL function can be applicable to achieve the cell reference of maximum salary.

Step 1: Finding Max Salary

The steps required steps are as follows:

• Initially, we obtain $35,000 in the H5 cell by inserting the formula below.

Step 2: Finding Employee Name with Maximum Salary

Then, we get Jack Alfred in the H8 cell, the employee who gets the maximum salary with the formula as follows. But didn’t pick Alisha Stokes who also gets the max salary of $35000.

Step 3: Finding the Corresponding Cell Location with the Maximum Value

In the cell H11, we input this formula:

This formula shows the cell reference $E$13. Because the E13 cell stores the first maximum value of $35000 in the Salary column.

2. Finding Max Value and Corresponding Cell from Multiple Excel Ranges

The MAX function not only considers adjacent cells but also non-adjacent cells. The function also finds the maximum value and corresponding cell location in Excel.

• Inserting the formula with non-adjacent cells, we achieve $35,000 in the H5 cell.

• Next, we get the Jack Alfred in the H8 cell by applying the formula as follows.

• Further, we get $E$15 cell reference of the highest value by applying the following formula.

Read More: How to Find Max Value in Range with Excel Formula

3. Using Excel Formulas to Find Corresponding Multiple Cells with Max Value

Unlike the previous method, in this section, we will find out multiple maximum values and their corresponding cell location by applying combinations of the TEXTJOIN-IF-MAX formula and the FILTER-MAX formula in Excel.

3.1 Combining TEXTJOIN and IF Functions

Here, we will combine TEXTJOIN and IF Functions to find corresponding multiple cells with Max Value.

By applying the MAX function we get the maximum value from the Salary range. Then IF function picks up the employee names who get the maximum salary. After that, the TEXTJOIN function adds all the names with a delimiter.

Please follow the necessary steps:

• By applying the following formula, we get Jack Alfred, Alisha Stokes in the H5 cell.

• Lastly, we obtain the E13, E14 cell references by inserting the formula as follows.

3.2 Merging FILTER, MAX and TEXTJOIN Functions

The MAX function determines the maximum value. Then, the FILTER function filters those maximum values and shows them.

Please check out the required steps:

• Enter the following formula in Cell G7 and you will see the employee name with maximum salary.

• Now enter the following formula in cell I7 and you will get the corresponding cell location of max values.

4. Finding Corresponding Multiple Cells with Max Value Based on Criteria

In this section, we will discuss how to find multiple maximum values and their corresponding cell references based on criteria. The combination of the MAX, IF, TEXTJOIN and TEXTSPLIT functions is applicable for single and multiple criteria; Besides, the combination of IF, MAXIFS, TEXTJOIN, and TEXTSPLIT functions is also useful for multiple criteria.

4.1 Combining MAX, IF, TEXTJOIN and TEXTSPLIT Functions for Single Criterion

Suppose we are likely to find the maximum salary of employees who joined after 2018. Using the MAX, IF, TEXTJOIN, and TEXTSPLIT functions we can find the multiple employees with max salary and their corresponding cell references based on a single criterion.

Please follow the necessary steps below:

• Enter the following formula in cell G9 and press Enter. You will get the employee names in an array format.

• Now enter the following formula in cell H9 and you will get the corresponding cell location of max values.

• Further, we get the cell locations of maximum salary by dragging the Fill Handle tool.

4.2 Joining MAX, IF, TEXTJOIN and TEXTSPLIT Functions for Multiple Criteria

Assume, we want to find the employees with the maximum salary who joined between 2020 to 2023. Using Excel MAX, IF, TEXTJOIN, and TEXTSPLIT functions we can find multiple employees with max salary and their corresponding cell references based on multiple criteria.

Please follow the necessary steps below:

• Write the following formula in cell G10 and press Enter. You will get the employee names in an array format.

• Now input the following formula in the H10 cell and you will get the corresponding cell reference of max values.

4.3 Merging IF, MAXIFS, TEXTJOIN and TEXTSPLIT Functions for Multiple Criteria

Like the previous method, we can also find multiple employees with max salary and their corresponding cell references based on multiple criteria. Here, we will use Excel MAXIFS, IF, TEXTJOIN, and TEXTSPLIT functions.

Please follow the necessary steps below:

• Insert the following formula in cell G10 and press Enter. You will get the employee names in an array format.

• Now write the following formula in the H10 cell and you will get the corresponding location that contains max values.

Read More: How to Find Maximum Value in Excel with Condition

5. Using VBA Macro to Find Multiple Max Values and Corresponding Cell Locations

In this section, you will learn to use the VBA Macro to find the max value and corresponding cell in Excel. As you know, Excel functions and formulas find max values but fail to determine corresponding cell locations from multiple ranges.

We are considering the dataset as follows which contains some random numbers and 120 is the highest number that is located in E8, E10, and C16 cells.

• Now, write the following VBA code in the Module and click on the Run command.

Sub Find_Multiple_Max_Values_And_Addresses_from_Range()
Dim rng As Range
Dim maxVal As Double
Dim results() As String
Dim i As Long
Dim CellAddress As String
Dim outputRange As Range
Set rng = Application.InputBox("Select a range:", Type:=8)
ReDim results(1 To rng.Cells.Count)
i = 1
maxVal = Application.WorksheetFunction.Max(rng)
For Each cell In rng
    If cell.Value = maxVal Then
        results(i) = cell.Address
        i = i + 1
    End If
Next cell
If i > 1 Then
    ReDim Preserve results(1 To i - 1)
    CellAddress = Join(results, ", ")
    Set outputRange = Application.InputBox("Select an output location:", Type:=8)
    outputRange.Value = maxVal
    outputRange.Offset(2, 0).Value = CellAddress
End If
End Sub

• Then Click as follows: Developer => Macro.
• Next, you will get a Macro dialog box and click Find_Multiple_Max_Values_and_Addresses_from_Range => Run.

Sub Find_Multiple_Max_Values_And_Addresses_from_Range()
[Code]
End Sub

Dim rng As Range
Set rng = Application.InputBox("Select a range:", Type:=8)

Dim results() As String
ReDim results(1 To rng.Cells.Count)

Dim maxVal As Double
maxVal = Application.WorksheetFunction.Max(rng)

For Each cell In rng
[Logical Statement]
Next cell

Dim i As Long
i = 1
If cell.Value = maxVal Then
        results(i) = cell.Address
        i = i + 1
End If

Dim CellAddress As String
If i > 1 Then
    ReDim Preserve results(1 To i - 1)
    CellAddress = Join(results, ", ")
End If

Dim outputRange As Range
Set outputRange = Application.InputBox("Select an output location:", Type:=8)
outputRange.Value = maxVal
outputRange.Offset(2, 0).Value = CellAddress

• Thus, an Input dialog box appears and selects $B$6:$E$18 range => OK.

• Next, another Input dialog box appears and selects the $H$5 range => OK.

• Therefore, we obtain the max value of 120 in the H6 cell and their cell locations $E$8, $E$10, $C$16 in the worksheet.

Download Practice Workbook

Conclusion

In short, the INDEX-MATCH formula with MAX, LARGE, AGGREGATE, or SUBTOTAL functions returns the first max value and the corresponding cell only. On the contrary, a combination of the TEXTJOIN-IF and FILTER-MAX-TEXTJOIN formulas returns two or multiple data for max values and corresponding cells. Besides, the combination of the MAX, IF, TEXTJOIN, and TEXTSPLIT Functions is useful when single and multiple criteria are applied. Further, the combination of IF, MAXIFS, TEXTJOIN, and TEXTSPLIT functions is also applicable to multiple criteria. Further, VBA Macro is essential to find out the maximum values and their cell locations from two or multiple ranges. Hope this article makes your learning easier. Please leave your thoughts, suggestions, and any queries in the comment section.

Related Articles

• Set a Minimum and Maximum Value in Excel
• Excel MIN and MAX in Same Formula
• How to Cap Percentage Values Between 0 and 100 in Excel

<< Go Back to Excel MAX Function | Excel Functions | Learn Excel