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.

**Table of Contents**hide

**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
to determine whether to search for an**Boolean Value****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 theargument of the**row_num****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 theargument.**lookup_array** **2**is theargument and**col_index_num**indicates the**FALSE****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.