Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

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

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)

Combine VLOOKUP and MAX Functions to Find Max Value and Corresponding Cell

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

Combine VLOOKUP and MAX Functions to Find Max Value and Corresponding Cell

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)

Apply INDEX-MATCH Functions to Find Max Value and Corresponding Cell

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))

Apply INDEX-MATCH Functions to Find Max Value and Corresponding Cell

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))

Apply INDEX-MATCH Functions to Find Max Value and Corresponding Cell

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

Apply INDEX-MATCH Functions to Find Max Value and Corresponding Cell


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))

Use FILTER and MAX Functions to Find Max Value and Corresponding Cell

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

Use FILTER and MAX Functions to Find Max Value and Corresponding Cell

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)

Combine MAX and IF Functions to Find Max Value and Corresponding Cell in Excel

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

Combine MAX and IF Functions to Find Max Value and Corresponding Cell in Excel

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))

Combine MAX and IF Functions to Find Max Value and Corresponding Cell in Excel

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))))

Combine MAX and IF Functions to Find Max Value and Corresponding Cell in Excel

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

Combine MAX and IF Functions to Find Max Value and Corresponding Cell in Excel


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)

Combine MAX and IF Functions to Find Max Value and Corresponding Cell in Excel

So, it is again Jack Alfred.

Combine MAX and IF Functions to Find Max Value and Corresponding Cell in Excel

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)

Combine MAX and IF Functions to Find Max Value and Corresponding Cell in Excel

See, that is Steve Moor.

Combine MAX and IF Functions to Find Max Value and Corresponding Cell in Excel

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.

Combining SUMPRODUCT and MAX Functions to Find Max Value for Corresponding Cell Based on Multiple 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.

Combining SUMPRODUCT and MAX Functions to Find Max Value for Corresponding Cell Based on Multiple Criteria

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

Combining SUMPRODUCT and MAX Functions to Find Max Value for Corresponding Cell Based on 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.


Further Readings

Rifat Hassan

Rifat Hassan

Hello! Welcome to my profile. Here I will be posting articles related to Microsoft Excel. I am a passionate Electrical Engineer holding a Bachelor’s degree in Electrical and Electronic Engineering from the Bangladesh University of Engineering and Technology. Besides academic studies, I always love to keep pace with the revolution in technology that the world is rushing towards day by day. I am diligent, career-oriented, and ready to cherish knowledge throughout my life.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo