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

**Table of Contents**hide

**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.

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

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

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

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

- Here
determines the maximum value in the range`MAX(B4:B22)`

**B4**to**B22**. That is 554. - And
first searches for that maximum value 554 in the first column`VLOOKUP(MAX(B4:B22),B4:E22,2,FALSE)`

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

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.

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.

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.

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

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

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

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

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:

**VLOOKUP Max of Multiple Values (With Alternative)****VLOOKUP to Return Multiple Values Horizontally in Excel****Excel VLOOKUP to Return Multiple Values Vertically****VLOOKUP to Return Multiple Columns in Excel (4 Examples)****VLOOKUP to Search Text in Excel (4 Easy Ways)****How to Use VLOOKUP for Multiple Columns in Excel****10 Best Practices with VLOOKUP in Excel**