Find Max Value and Corresponding Cell in Excel (5 Methods) - ExcelDemy (2024)

Dataset Overview

Finding the maximum value and its corresponding cell involves identifying the highest value(s) within a dataset and determining the location of that value. In this tutorial, you’ll learn how to find the maximum value and its associated cell in an Excel spreadsheet.
Let’s take a look at the following image. It displays an employee dataset containing information such as Employee ID, Name, Joining Year, and Salary. We’ve already identified the employee with the highest salary and pinpointed the cell location for that value.

To achieve this, you can utilize Excel formulas and VBA code. Some Excel formulas will return the first maximum value along with its corresponding cell from the dataset. Other formulas can provide all the cells associated with the maximum value. Additionally, we’ll demonstrate how to determine the maximum value and its corresponding cell based on specific criteria.

Method 1 – Using an Excel Formula to Find the First Maximum Value and Corresponding Cell

By combining Excel’s INDEX and MATCH functions with other relevant functions (such as MAX, LARGE, AGGREGATE, and SUBTOTAL), you can identify the first maximum value and its corresponding cell within a single dataset.

Let’s consider the sample dataset below, which includes Employee ID, Employee Name, Joining Year, and Salary. In cells C13 and C14, we have Jack Alfred and Alisha Stokes, both earning the maximum salary of $35,000. However, the INDEX-MATCH formula will return only Jack Alfred since it appears first in the dataset.

To determine the maximum salary, apply the MAX function, use the INDEX-MATCH formula, and then find the cell reference for the maximum salary. Subsequently, you can use the CELL function to determine the cell reference for the maximum salary.

Step 1 – Finding the Maximum Salary

To determine the maximum salary, follow these steps:

1. In cell H5, enter the following formula:

Step 2 – Identifying the Employee with the Highest Salary

We want to find Jack Alfred, the employee associated with the maximum salary. In cell H8, enter the following formula:

This will display the name of the employee (e.g., Jack Alfred) who receives the highest salary.

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

To determine the cell location associated with the maximum value (in this case, the highest salary), follow these steps:

1. In cell H11, enter the following formula:

This formula will display the cell reference, which is $E$13. The value in cell E13 represents the first maximum salary of $35000 in the Salary column.

Method 2 – Finding Maximum Value and Corresponding Cell from Multiple Excel Ranges

The MAX function not only considers adjacent cells but also non-adjacent cells. It allows you to find the maximum value and its corresponding cell location in Excel.

• To find the maximum value from non-adjacent cells, enter the following formula:

This formula will give you the highest value, which in this case is $35,000, located in cell H5.

• Let’s find the corresponding cell for the name Jack Alfred.Enter the following formula:

This will return the value Jack Alfred located in cell H8.

• To get the cell reference for the highest value (which is in cell E15), enter the following formula:

This will give you the cell reference $E$15.

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

Method 3 – Using Excel Formulas to Find Corresponding Multiple Cells with Maximum Value

In this section, we’ll explore a method to find multiple maximum values and their corresponding cell locations by combining the TEXTJOIN-IF-MAX formula and the FILTER-MAX formula in Excel.

3.1 Combining TEXTJOIN and IF Functions

1. Apply the MAX function to determine the maximum value from the Salary range.
2. Use the IF function to identify the employee names who receive the maximum salary.
3. Utilize the TEXTJOIN function to concatenate all the names with a delimiter (such as a comma).

To achieve this, follow these steps:

• In cell H5, enter the following formula:

This will give you the names Jack Alfred and Alisha Stokes.

• To obtain the cell references E13 and E14, enter this formula:

3.2 Merging FILTER, MAX and TEXTJOIN Functions

1. The MAX function identifies the maximum value from the Salary range.
2. The FILTER function then filters the employee names associated with these maximum values.

Follow these steps:

• In cell G7, enter the formula:

  =FILTER(C6:C18,E6:E18=MAX(E6:E18)) 

This will display the employee name with the highest salary.

• For the corresponding cell locations of the maximum values, enter this formula in cell I7:

Method 4 – Finding Corresponding Multiple Cells with Maximum Value Based on Criteria

In this section, we’ll explore how to identify multiple maximum values and their corresponding cell references based on specific criteria. We’ll use a combination of the MAX, IF, TEXTJOIN, and TEXTSPLIT functions. Additionally, we’ll discuss how to adapt this approach for both single and multiple criteria using the IF, MAXIFS, TEXTJOIN, and TEXTSPLIT functions.

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

Suppose we want to find the maximum salary among employees who joined after 2018. By using the following functions, we can determine the employees with the highest salaries and their corresponding cell references based on this single criterion:

• Start by entering the formula below in cell G9 and pressing Enter. This will provide an array of employee names:

• Enter the following formula in cell H9 to obtain the cell location of the maximum values:

• To find the cell locations with the maximum salary, use the Fill Handle tool.

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

Let’s assume that we want to find employees with the maximum salary who joined between 2020 and 2023. By using Excel’s MAX, IF, TEXTJOIN, and TEXTSPLIT functions, we can identify multiple employees with the highest salary and their corresponding cell references based on multiple criteria.

Follow these steps:

• Enter the following formula in cell G10 and press Enter. This will give you the employee names in an array format:

• Enter the following formula in cell H10 to get the corresponding cell reference for the maximum values:

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

Similar to the previous method, we can find multiple employees with the maximum salary and their corresponding cell references based on multiple criteria. In this case, we’ll use Excel’s MAXIFS, IF, TEXTJOIN, and TEXTSPLIT functions.

Follow these steps:

• Insert the following formula in cell G10 and press Enter. This will give you the employee names in an array format:

• Enter the following formula in cell H10 to get the corresponding location that contains the maximum values:

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

Method 5 – Using VBA Macro to Find Multiple Maximum Values and Corresponding Cell Locations

In this section, you’ll learn how to use a VBA Macro to find the maximum value and its corresponding cell in Excel. While Excel functions and formulas can find maximum values, they don’t directly provide cell locations for those values when dealing with multiple ranges.

Let’s consider the following dataset, which contains some random numbers. The highest number, 120, appears in cells E8, E10, and C16.

Follow these steps:

• Enter the following VBA code in a Module and click the Runcommand:

Sub Find_Multiple_Max_Values_And_Addresses_from_Range()Dim rng As RangeDim maxVal As DoubleDim results() As StringDim i As LongDim CellAddress As StringDim outputRange As RangeSet rng = Application.InputBox("Select a range:", Type:=8)ReDim results(1 To rng.Cells.Count)i = 1maxVal = Application.WorksheetFunction.Max(rng)For Each cell In rng If cell.Value = maxVal Then results(i) = cell.Address i = i + 1 End IfNext cellIf 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 = CellAddressEnd IfEnd Sub

• Click the Developer tab, then select Macro.
• In the Macro dialog box, choose Find_Multiple_Max_Values_and_Addresses_from_Range and click Run.

Sub Find_Multiple_Max_Values_And_Addresses_from_Range()[Code]End Sub

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

Dim results() As StringReDim results(1 To rng.Cells.Count)

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

For Each cell In rng[Logical Statement]Next cell

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

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

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

To summarize the process:

• An input dialog box appears, allowing the user to select the range $B$6:$E$18.
• After selecting the range, the user clicks OK.

• Another input dialog box appears, prompting the user to select the range $H$5.
• The user selects $H$5 and clicks OK.

• The maximum value (120) is placed in cell H6.
• The cell locations $E$8, $E$10, and $C$16 are also recorded as having the maximum value.

Download Practice Workbook

You can download the practice workbook from here:

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