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

**Table of Contents**hide

**Download Practice Workbook**

**How to Find the Maximum Value and the Corresponding Cell in Excel**

Let us have a look at this data set. We have the employee record of a company named North Group.

We have Employee IDs, Employee Names and their Salaries in columns **B, C** and **D** respectively.

**1. Finding Maximum Value and the Corresponding Cell Using VLOOKUP Function**

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

We can accomplish this by Excelâ€™s **VLOOKUP()** function.

To know details about the **VLOOKUP()** function in Excel, visit this post.

The formula that we shall use is:

`=VLOOKUP(MAX(B4:B19),B4:C19,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, **VLOOKUP() **will not work.

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

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

**2. Finding Maximum Value and the Corresponding Cell Using INDEX-MATCH Functions**

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(B4:D19,MATCH(MAX(D4:D19),D4:D19,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(`

`B4:D19`

`,MATCH(MAX(`

`D4:D19`

`),`

`D4:D19`

`,0),2))`

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

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

`B6:D21`

`,MATCH(MAX(`

`B6:B21`

`),`

`B6:B21`

`,0),3))`

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

**3. Finding Multiple Maximum Value Using FILTER Function**

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 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(C4:C19,D4:D19=MAX(D4:D19))`

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

**Note: FILTER()** function is only available in **office365**.

**4. Finding Maximum Value with Criteria Using MAX and IF Functions**

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.

**4.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(B4:D19,MATCH(MAX(IF(D4:D19>20000,B4:B19)),B4:B19,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(B4:D19,MATCH(MAX(IF(D4:D19>20000,B4:B19)),B4:B19,0),2))`

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

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 a similar criteria.

The formula will be:

`=FILTER(C4:C19,B4:B19=(MAX(IF(D4:D19>20000,B4:B19))))`

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

**4.2 Multiple Criteria**

**4.2.1 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(B4:D19,MATCH(MAX(IF(((B4:B19>100)*(B4:B19<500)),D4:D19)),D4:D19,0),2)`

So, it is again Jack Alfred.

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

`=CELL("address",INDEX(B4:D19,MATCH(MAX(IF(((B4:B19>100)*(B4:B19<500)),D4:D19)),D4:D19,0),2))`

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

**4.2.2 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 400, the formula will be:

`=INDEX(B4:D19,MATCH(MAX(IF(((B4:B19<200)+(B4:B19>500)),D4:D19)),D4:D19,0),2)`

See, that is Steve Moor.

And to get the Cell Reference, use this formula:

`=CELL("address",INDEX(B4:D19,MATCH(MAX(IF(((B4:B19<200)+(B4:B19>500)),D4:D19)),D4:D19,0),2))`

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

**Conclusion**

Using these methods, you can extract the maximum values 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.