Rifat Hassan
In Excel, the VLOOKUP function is one of the most used functions. It’s a quite versatile function of Excel. We can calculate the max value of a dataset by using the VLOOKUP function in Excel. In this article, we will discuss some easy steps to calculate the max value using the VLOOKUP function in Excel. So, let’s start the article and explore these steps.
Download Practice Workbook
Introduction to Excel 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 dataset. We have the Employee Record of Kingfisher Group.
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 that it finds.
So the VLOOKUP function has 4 inputs.
- A Lookup Value
- A Lookup Array
- A Column Index Number
- A Boolean Value to determine whether to search for an Approximate Match or an Exact Match (Optional). TRUE for the approximate match, FALSE for an exact match. The 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,B5:E17,2,FALSE)Formula Breakdown
- Here the lookup_array is from cell B4 to E22.
- The lookup_value is 178.
- VLOOKUP fnction, 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 that 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 “Ruhin Malik”?
Yes. You are right. The formula is-
=VLOOKUP("Ruhin Malik",C5:E17,3,FALSE)
See, the joining date of Ruhin Malik is 4-Aug-16.
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 topic. Here, we will learn the steps to find the max value using the VLOOKUP function in Excel.
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.
Firstly, 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.
In this case, the formula will be
=MAX(B5:B17)So the complete VLOOKUP formula will be-
=VLOOKUP(MAX(B5:B17),B5:E17,2,FALSE)Formula Breakdown
- Here, MAX(B5:B17) is the maximum value in the range B5 to B17. That is 490.
- And VLOOKUP(MAX(B5:B17),B5:E17,2,FALSE), irst searches for that maximum value 490 in the first column B of the given lookup_array, B5 to E17.
- 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 C17. It contains our desired result, Jennnifer Austin. The formula returns that.
See we have got the employee with the maximum ID, “Jennnifer Austin”.
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:D17),D4:E17,2,FALSE)
See, the joining date of the employee with the highest salary is 1-Jun-18.
Note: If you want, you can extract the minimum value in place of the maximum value. Just use the MIN function in place of the MAX function.
Read More: How to Return the Highest Value Using VLOOKUP in Excel
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.
Read More: INDEX MATCH vs VLOOKUP Function (9 Examples)
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,D5:E17,2,FALSE)And you will get the desired result too.
But only one problem. Few other Employees also get $25000 as salary.
But you have 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.
Read More: Use VLOOKUP with Multiple Criteria in Excel (6 Methods + Alternatives)
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 180?
The answer is there is no employee with ID 180.
So, if you use this formula
=VLOOKUP(180,B5:E17,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 Ruhin Malik. Because she is the closest with ID 180.
Now if you search for the Employee with ID 190 rather than 180, you still should get Ruhin Malik. Because still, he is the closest.
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.
Similar Readings
- VLOOKUP Not Working (8 Reasons & Solutions)
- Excel LOOKUP vs VLOOKUP: With 3 Examples
- What Is a Table Array in VLOOKUP? (Explained with Examples)
- Excel VLOOKUP to Return Multiple Values Vertically
- Use of VBA VLOOKUP to Find Values from Another Worksheet in Excel
Alternatives of the VLOOKUP Function
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(B5:E17,MATCH(MAX(D5:D17),D5:D17,0),2)Formula Breakdown
- First, the MAX(D5:D17) function, gives us the maximum value from the Salary column.
- Then, the MATCH function returns the row_num argument of the INDEX function.
- Finally, the INDEX function gives us the Employee Name with the highest Salary.
See, we have got the Employee with the highest salary. It is Steve Moor.
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(B5:E17,MATCH(MAX(E5:E17),E5:E17,0),1)
So, the ID of the most recent employee is 101.
Read More: VLOOKUP and Return All Matches in Excel (7 Ways)
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)
See, we have got the names of all the employees with a salary of $25000.
Note: FILTER function is only available in Office365.
Read More: VLOOKUP with Multiple Matches in Excel
3. Maximum Value Based on Some Criteria
Again, can you find out the Employee with the maximum ID, with a salary greater than $10000?
You can find this out using the VLOOKUP and MAXIFS functions of Excel
=VLOOKUP(MAXIFS(B5:B17,D5:D17,">10000"),B5:E17,2,FALSE)Formula Breakdown
- Firstly, the MAXIFS function will return the lookup_value argument of the VLOOKUP function.
- Here, B5:B17 is the lookup_array argument.
- 2 is the col_index_num argument and FALSE indicates the exact match option.
- Finally, the VLOOKUP function will return the Employee Name with the highest ID which is Jennnifer Austin.
So, the employee with the maximum ID and salary greater than $20000 is Jennnifer Austin.
Note: MAXIFS function is available in Office 365 and from Excel 2019.
Read More: VLOOKUP with Multiple Criteria in Excel (6 Examples)
How to Find Latest Date Using VLOOKUP Function in Excel
In this section of the article, we will discuss the steps to find the latest date from a dataset using the VLOOKUP function in Excel. Let’s say, we have a dataset where we have the Name and amount of Money Deposited by some customers of a bank along with the Dates. Our goal is to find the latest date using the VLOOKUP function. Let’s use the steps mentioned below to do this.
Steps:
- Firstly, use the following formula in cell C20.
=VLOOKUP(MAX(B5:B18),B4:D18,1,FALSE)- Following that, press ENTER.
That’s it! Now, you will be able to see the latest date in cell C20 as shown in the following picture.
Conclusion
Therefore, 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.
