Today I will be showing how you can find the max value and the corresponding cell from a set of data in **Microsoft Excel**.

**Download Practice Workbook**

**5 Quick Ways to Find Max Value and Corresponding Cell in Excel**

Let us have a look at this data set. We have the employee record of a company containing “**Employee ID**“, “**Employee Name**“, and “**Salary**“. Now we will find the max value and corresponding cell from the dataset.

**1. Combining VLOOKUP and MAX Functions**

First of all, let us try to extract the name of the employee with the maximum ID.

We can accomplish this with Excel’s **VLOOKUP** **function** along with the help of the **MAX function**.

**Steps:**

- First, choose a
**cell**(**G4**) and write the formula below-

`=VLOOKUP(MAX(B5:B17),B5:C17,2,FALSE)`

See, we have got **Kane** **Simpson**, the employee with the **maximum** **ID**.

But there are two aspects to look deep into.

First, the desired value **Employee** **Name** is in a **column** right to the lookup value **Employee** **ID**.

But if it is on the left, for example, if we want to find out the name of the **employee** with the **maximum** **salary**, the **VLOOKUP function **will not work.

Second, if you want to know the cell reference of the employee with the **maximum** **ID**, rather than the **name**, the **VLOOKUP function** is not the right choice for you.

The best choice for both these cases is the combination of the **INDEX** and **MATCH** functions of Excel.

**Read more:** **How to Find Maximum Value in Excel with Condition**

**2. Appling INDEX-MATCH to Find Max Value and Corresponding Cell**

To know details about the **INDEX** and the **MATCH** functions of Excel, visit this **link.**

Let’s first try to find out the name of the Employee with the maximum salary.

Can you guess the formula?

Yes. You are right. The formula will be:

`=INDEX(B5:D17,MATCH(MAX(D5:D17),D5:D17,0),2)`

See, we have got the employee with the maximum salary-**Jack Alfred**.

But now if one asks you what the cell reference of the employee with the maximum salary is, then?

Can you extract that using a formula?

Yes. Actually, you can extract that using a combination of **CELL** and **INDEX-MATCH** **functions** of Excel pretty conveniently.

To know details about the **CELL** **function** of Excel, visit this **link.**

The formula that we shall use here is:

`=CELL("address",INDEX(B5:D17,MATCH(MAX(D5:D17),D5:D17,0),2))`

See, we have got the cell reference of the employee with the maximum salary. It is **Jack** **Alfred** in **C12**.

Now can you guess the formula to determine the cell reference of the salary of the employee with the maximum ID?

Yes. You are right. The formula will be:

`=CELL("address",INDEX(B5:D17,MATCH(MAX(B5:B17),B5:B17,0),3))`

See, the salary of the employee with the maximum ID is **$20000**. It is in cell **D16**.

**3. Use FILTER and MAX Functions to Find Max Value and Corresponding Cell**

If you notice the previous section carefully, you will see that there is still a problem.

When we got the employee with the maximum salary, we got **Jack** **Alfred**.

But there is another employee who gets the maximum salary, **$35000**. She is **Alisha** **Stokes** in cell **C15**.

But the **INDEX-MATCH** formula has provided us with only the first one it has got.

So what is the solution to this problem?

You can use the** FILTER** **function** of Excel to extract all the values together.

To know details about the **FILTER** **function**, visit this **link.**

The formula that we shall use is:

`=FILTER(C5:C17,D5:D17=MAX(D5:D17))`

See, we have got both the employees with the maximum salary, **Jack** **Alfred** and **Alisha** **Stokes**.

**Note:** The** FILTER** function is only available in **office365**.

**4. Combining MAX with IF Function to Find Corresponding Cell**

Now the last problem. If someone asks you, what is the name of the employee with the **maximum** **ID**, but salary greater than **$20000**, what will you do?

Quite Simple. You can use the **INDEX-MATCH** or **FILTER **functions as mentioned above, with a combination of **MAX** and **IF** functions of Excel.

Follow the examples below.

**Case 1: Single Criteria**

To extract the name of the employee with the maximum ID with a single criteria, salary greater than** $20000**, the formula will be:

`=INDEX(B5:D17,MATCH(MAX(IF(D5:D17>20000,B5:B17)),B5:B17,0),2)`

So, the employee with the **maximum** **ID** but salary greater than **$20000** is **Jack** **Alfred**.

And obviously, if you want, you can extract the cell reference of the employee with the maximum ID but salary greater than $20000.

The formula will be:

`=CELL("address",INDEX(B5:D17,MATCH(MAX(IF(D5:D17>20000,B5:B17)),B5:B17,0),2))`

See, the required cell reference is** C12**.

Now, If you wish, you can write the formula using the **FILTER** **function** too. It will come in handy when there is more than one employee who meets similar criteria.

The formula will be:

`=FILTER(C5:C17,B5:B17=(MAX(IF(D5:D17>20000,B5:B17))))`

See, now we have got both **Jack** **Alfred** and **Gregory** **Mills**, the employees with the **maximum** **ID** but salary greater than **$20000**.

**Case 2: For Multiple Criteria**

**I. AND Type Multiple Criteria**

Now let’s do another interesting thing.

Try to find out which employee has the **maximum** **salary**, but has an **ID** between **100** to **500**.

It is a problem of AND Type Multiple Criteria.

The formula will be:

`=INDEX(B5:D17,MATCH(MAX(IF(((B5:B17>100)*(B5:B17<500)),D5:D17)),D5:D17,0),2)`

So, it is again **Jack** **Alfred**.

And if you want, you can extract the cell reference using this formula:

`=CELL("address",INDEX(B5:D17,MATCH(MAX(IF(((B5:B17>100)*(B5:B17<500)),D5:D17)),D5:D17,0),2))`

We see the required cell reference is **C12**.

**ii. OR Type Multiple Criteria**

And to find out the employee with the maximum salary, but the ID of either less than **200** or greater than **500**, the formula will be:

`=INDEX(B5:D17,MATCH(MAX(IF(((B5:B17<200)+(B5:B17>500)),D5:D17)),D5:D17,0),2)`

See, that is **Steve** **Moor**.

And to get the Cell Reference, use this formula:

`=CELL("address",INDEX(B5:D17,MATCH(MAX(IF(((B5:B17<200)+(B5:B17>500)),D5:D17)),D5:D17,0),2))`

See, the required cell reference is **C9**. Thus we calculated max value and corresponding cell in excel.

### 5. Combining SUMPRODUCT and MAX to Find Max Value Based on Multiple Criteria

If you want you can combine the **SUMPRODUCT** and **MAX functions **to find the Max value for the corresponding cell.

Suppose we have a dataset with multiple criteria “**Joining** **Year**” and “**Salary**”. Now we will determine the maximum value for the corresponding cell according to those criteria.

**Steps:**

- First, select a
**cell**(**H6**) and apply the following formula-

`=SUMPRODUCT(MAX((((D5:D17=H4)*(E5:E17=H5)*(B5:B17)))))`

Where,

- The
**MAX function**will search for the highest value in a set of data inside the argument where the**output**stands as**99**. - The
**SUMPRODUCT function**will return the sum of the product into an array.

- Simply, press
**ENTER**. - Finally, we will get the corresponding cell with the maximum value based on the multiple criteria.

**Conclusion**

Using these methods, in Excel you can find the max value and corresponding cell references from any set of data quite convincingly, satisfying any criteria. Do you know any other method? Let us know in the comment section.