## 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.

*Note:** The INDEX-MATCH formula will only return the first occurrence if there are multiple maximum values.*

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:

- In cell
**H5**, enter the following formula:

`=MAX(E6:E18)`

**Note**

This will display the maximum value from the range **E6** to **E18**.

**Note:**You can achieve the same result using any of the following alternative formulas:

`=LARGE(E6:E18,1)`

`=AGGREGATE(4,6,E6:E18)`

`=SUBTOTAL(4,E6:E18)`

Remember that **Alisha Stokes** also has the same maximum salary of **$35,000,** but the formula specifically identifies Jack Alfred in this case.

**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:

`=INDEX(B6:E18,MATCH(MAX(E6:E18),E6:E18,0),2)`

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

**Note**

**Note: **You can also achieve the same result using these alternative formulas:

- Formula 1:

`=INDEX(B6:E18,MATCH(LARGE(E6:E18,1),E6:E18,0),2)`

- Formula 2:

`=INDEX(B6:E18,MATCH(AGGREGATE(4,6,E6:E18),E6:E18,0),2)`

- Formula 3:

`=INDEX(B6:E18,MATCH(SUBTOTAL(4,E6:E18),E6:E18,0),2)`

These alternatives will also give you the name of the employee with 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:

- In cell
**H11**, enter the following formula:

`=CELL("address", INDEX(B6:E18, MATCH(MAX(E6:E18), E6:E18, 0),4))`

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.

**Note**

**Note:**You can achieve the same result using these alternative formulas:

- Formula 1:

`=CELL("address",INDEX(B6:E18,MATCH(LARGE(E6:E18,1),E6:E18,0),4))`

- Formula 2:

`=CELL("address",INDEX(B6:E18,MATCH(AGGREGATE(4,6,E6:E18),E6:E18,0),4))`

- Formula 3:

`=CELL("address",INDEX(B6:E18,MATCH(SUBTOTAL(4,E6:E18),E6:E18,0),4))`

- Formula 4:

`=ADDRESS(MATCH(MAX(E6:E18), E6:E18, 0) + ROW(E5), 5)`

- Formula 5:

`="$E$"&MATCH(MAX(E6:E18),E6:E18,0)+ROW(E5)`

These alternatives will also provide you with the cell reference for the maximum salary value.

*The CELL function has the following syntax: CELL(info_type,[reference]). The reference parameter is optional. If omitted, the CELL function returns the cell reference itself.*

**Formula Breakdown**

- Let’s break down the formula step by step:
**MAX(E6:E18)**: This part of the formula calculates the maximum value within the range E6:E18. In this case, it returns 35000, which is the highest salary.**MATCH(35000, E6:E18, 0)**: The MATCH function searches for the value 35000 within the E6:E18 range. It returns the row number where this value is found. In this case, it returns 8 because the maximum salary (35000) is in the 8th row.**INDEX(B6:E18, 8, 2)**: The INDEX function retrieves the value from a specific cell within the B6:E18 range. Here, it returns the value in the 8th row and 2nd column, which corresponds to cell $E$13.

So, the final result is that the formula points to cell

**$E$13**, where the first maximum salary of**$35000**is stored.

## 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:

`=MAX(E6:E12,E15:E20)`

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:

`=INDEX(B6:E20,MATCH(MAX(E6:E12,E15:E20),E6:E20,0),2)`

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:

`=CELL("address",INDEX(B6:E20,MATCH(MAX(E6:E12,E15:E20),E6:E20,0),4))`

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

- Apply the
**MAX**function to determine the maximum value from the Salary range. - Use the IF function to identify the employee names who receive the maximum salary.
- 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:

`=TEXTJOIN(", ", TRUE, IF(E6:E18=MAX(E6:E18), C6:C18, ""))`

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

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

`=TEXTJOIN(", ", TRUE, IF(E6:E18=MAX(E6:E18), "E" & ROW(E6:E18), ""))`

### 3.2 Merging FILTER, MAX and TEXTJOIN Functions

- The
**MAX**function identifies the maximum value from the Salary range. - 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:

`=TEXTJOIN(", ", TRUE, IF(E6:E18=MAX(E6:E18), "E" & ROW(C6:C18), ""))`

## 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:

`=IFERROR(TEXTSPLIT(TEXTJOIN(",", TRUE,IF((D6:D18 > H5)*(E6:E18 = MAX(IF(D6:D18 > H5, E6:E18))), C6:C18, ""),),,","),"")`

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

`=IFERROR(CELL("address",INDEX($E$6:$E$18, MATCH(G9, $C$6:$C$18, 0),1)),"")`

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

*Note:** Dragthe Fill Handle tool until you identify the cell location for each employee.*

### 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:

`=IFERROR(TEXTSPLIT(TEXTJOIN(",", TRUE, IF((D6:D18 >=H5)*(D6:D18 <=H6), IF(E6:E18 = MAX(IF((D6:D18 >=H5)*(D6:D18 <=H6), E6:E18)), C6:C18, ""), ""),),,","),"")`

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

`=IFERROR(CELL("address",INDEX($E$6:$E$18, MATCH(G10, $C$6:$C$18, 0),1)),"")`

### 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:

`=IFERROR(TEXTSPLIT(TEXTJOIN(",", TRUE, IF((D6:D18 >=H5)*(D6:D18 <=H6), IF(E6:E18 =MAXIFS(E6:E18, D6:D18, ">="&H5, D6:D18, "<="&H6), C6:C18, ""), "")),,","),"")`

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

`=IFERROR(CELL("address",INDEX($E$6:$E$18, MATCH(G10, $C$6:$C$18, 0),1)),"")`

**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**Run**command:

`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**.

**Code Breakdown**

- We created a sub-procedure named
**Find_Multiple_Max_Values_And_Addresses_from_Range**.

`Sub Find_Multiple_Max_Values_And_Addresses_from_Range()[Code]End Sub`

- We prompt the user to select a range using the
**Application.InputBox**property and assign it to the**rng**variable.

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

- We count the cells in the rng range using
**Cells.Count**property and initialize an array called**results().**

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

- Calculate the maximum value within the rng range using the
**Application.WorksheetFunction.Max(rng)**statement.

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

- We iterate through each cell in the
**rng**range to extract cell address based on a logical statement.

`For Each cell In rng[Logical Statement]Next cell`

- We compare the value of each cell with the maximum value. If it matches, we store the cell address in the results () array.

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

- If there are multiple cell addresses with the maximum value, we combine them using the Excel
**Join**property.

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

- We prompt the user to select the output locations using the
**outputRange**variable (which is declared as a**Range)**.

`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:

**Find Max Value and Corresponding Cell.xlsm**

## 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**