VLOOKUP Max Value in Excel (With Limitations and Alternative Options)

Finding Maximum value using VLOOKUP in Excel

Today I will be showing how you can extract the max (maximum) value from a database using the VLOOKUP function of Microsoft Excel.


Download Practice Workbook


An Introduction to Excel’s VLOOKUP Function 

Before diving into the main discussion, I would like to provide a short discussion about the VLOOKUP function of Excel.

First, have a look at this data set. We have the Employee Record of a company named Kingfisher Group.

A data set in Excel

We have the Employee IDs, Employee Names, their Salaries and Joining Dates in columns B, C, D and E respectively.

Now if someone asks you the name of the Employee with ID 178, how will you extract that?

You will probably look through all the values in column B (Employee ID) to find out where 178 is. And after you find one, you will move to one step right in the same row, and see what the corresponding name is.

In this case, it is Natasha Marlo.

But think, if you have a database of 10,000 rows long, is it possible to extract out any data manually? Like this?

The VLOOKUP function of Excel does just the same thing for you. It matches any given value with the first column of a given data set.

After it finds one match, it moves a specific number of steps right (This is also an input) and returns the value which it finds.

So the VLOOKUP function has four inputs.

  • A Lookup Value
  • A Lookup Array
  • A Column Index Number
  • A Boolean Value to Determine whether to Search for Approximate Match or Exact Match (Optional). TRUE for the approximate match, FALSE for an exact match. Default is TRUE.

Therefore, the syntax of the VLOOKUP() function is

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

Let us go back to our previous problem. We have to find out the name of the employee with ID 178.

So our formula will be

=VLOOKUP(178,B4:E22,2,FALSE)

VLOOKUP function in Excel

Now let’s explain the formula.

Here the lookup_array is from cell B4 to E22.

And the lookup_value is 178.

VLOOKUP(), therefore, searches for 178 in the first column of the lookup_array, which is column B.

It finds an exact match in cell B12.

The col_index_num is 2. So after finding the match, it moves to column number 2 of the lookup_array, of the same row.

Column number 2 of the lookup_array in column C.

So it moves to cell C12.

And finally, it returns the data which it finds there.

In this case, it is Natasha Marlo.

Now if you understand the above problem, can you tell me what the formula will be to determine the joining date of Alisha Stokes?

Yes. You are right. The formula is

=VLOOKUP("Alisha Stokes",C4:E22,3,FALSE)

VLOOKUP function used in Excel

See, the joining date of Alisha Stokes is 2-Jul-01.

I hope you have understood how the VLOOKUP() function works. Now we shall move to our main problem.


How to Get the Max Value Using VLOOKUP Function

Now we have come to our main problem.

If I ask you what the name of the employee with the maximum Employee ID is, can you find that?

In case you understand how the VLOOKUP() function of Excel works, this should be very easy for you.

First, we have to know what the maximum Employee ID is. Then we have to find out the name of the employee with that.

To find out the maximum ID, we will use the MAX() function of Excel.

The syntax of the MAX() function is

=MAX(number_1,[number_2],...)

In this case, the formula will be

=MAX(B4:B22)

So the complete VLOOKUP formula will be

=VLOOKUP(MAX(B4:B22),B4:E22,2,FALSE)

MAX and VLOOKUP functions in Excel

See we have got the Employee with the maximum ID, Ijack Simpson.

  • Here MAX(B4:B22) determines the maximum value in the range B4 to B22. That is 554.
  • And VLOOKUP(MAX(B4:B22),B4:E22,2,FALSE) first searches for that maximum value 554 in the first column B of the given lookup_array, B4 to E22.
  • After it finds a match, it moves to the same row of the column given as col_index_num. As in this case, col_index_num is 2, it moves to the same row of the 2nd column of the lookup_array, that is column C.
  • The resultant cell is C18. It contains our desired result, Ijack Simpson. The formula returns that.

Now can you tell me what the formula will be to determine the joining date of the employee with the highest salary?

Yes. You have guessed right. The formula will be

=VLOOKUP(MAX(D4:D22),D4:E22,2,FALSE)

Finding Maximum value using VLOOKUP in Excel

See, the joining date of the employee with the highest salary is 6-May-15.

Note: If you want, you can extract out the minimum value in place of the maximum value. Just use the MIN() function in place of MAX().


Limitations of the VLOOKUP Function


1. Desired Value in a Column Left to the Lookup Column

Now, if I ask you, what is the ID of the Employee who joined most recently? Or what is the name of the Employee who gets the maximum salary?

Can you find these using VLOOKUP?

The answer is no.

The biggest limitation of VLOOKUP is that you can not use it when the desired value is in a column left to the lookup column.

As the Employee ID column is to the left of the Joining Date column, we can not find out the ID of the employee who joined most recently.

Same for the name of the Employee who gets the maximum salary.


2. Multiple Values Matching the Criteria

Now another question. Let me ask you what the joining date of the employee is who gets a salary of $25000.

I am sure you will use this formula

=VLOOKUP(25000,D4:E22,2,FALSE)

And you will get the desired result too.

VLOOKUP Formula used in Excel

But only one problem. Few other Employees also get $25000 as salary.

But you have got information about only the one who comes first on the list. In this case John Smith. He joined in 1-May15.

So, the VLOOKUP function only works with the first value in the list when multiple values match the criteria.

This is another limitation of the VLOOKUP function.


3. Always Shows the Previous Value in Case of an Approximate Match

Now the last question. What is the name of the Employee with ID 344?

The answer is there is no employee with ID 344.

So, if you use this formula

=VLOOKUP(344,B4:E22,2,FALSE)

You will get N/A as the result (Try this).

But the above formula searches for an exact match. If you insert TRUE in place of FALSE in the above formula, it will search for an approximate match.

And you will get Alisha Stokes. Because she is the closest with ID 343.

VLOOKUP formula in Excel

Now if you search for the Employee with ID 342 rather than 344, you still should get Alisha Stokes. Because still, she is the closest.

Try this and see what happens. This time you will get Kyle Johnson, not Alisha Stokes, though he is far from 342 with ID 287.

A VLOOKUP formula in Excel

This is another limitation of using the VLOOKUP() function. It always shows the previous value, not the next, in case of an appropriate match. No matter who is closer.

But do not worry. There are alternative solutions to all the limitations addressed above.


Alternative Options


1. Desired Value in a Column Left to the Lookup Column

To find out the name of the Employee who gets the highest salary, you can this combination of INDEX() and MATCH() functions

=INDEX(B4:E22,MATCH(27000,D4:D22,0),2)

An INDEX-MATCH formula in Excel

See, we have got the Employee with the highest salary. It is Jack Alfred.

Now can you tell me the formula to extract the ID of the employee who joined most recently?

Yes. You are right. The formula will be

=INDEX(B4:E22,MATCH(MAX(E4:E22),E4:E22,0),1)

INDEX-MATCH Function in Excel

So, the ID of the most recent employee is 423.


2. Multiple Values Matching the Criteria

To extract out all the Employees who get $25000 as salary, you can use this formula of FILTER() function

=FILTER(C4:C20,D4:D20=25000)

FILTER function in Excel

See, we have got the names of all the employees with a salary of $25000.

Note: FILTER() function is only available in Office365.


3. Maximum Value Based on Some Criteria

Can you find out the Employee with the maximum ID, with a salary greater than $20000?

You can find this out using the VLOOKUP and MAXIFS function of Excel

=VLOOKUP(MAXIFS(B4:B22,D4:D22,">10000"),B4:E22,2,FALSE)

VLOOKUP and MAXIFS Function in Excel

So, the employee with the maximum ID and salary greater than $20000 is Ijack Simpson.

Note: MAXIFS() function is available in office 365 and from Excel 2019.


Conclusion

Using these methods, you can use the VLOOKUP function and some alternatives to achieve the maximum value from a set of data. Do you know of 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 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