Undoubtedly, **VLOOKUP** is a versatile and handy function in **Excel**. Now, wouldn’t it be great if we could obtain the largest value with the **VLOOKUP function**? Sounds complex, right? Wrong! In this article, we’ll demonstrate 4 handy ways to return the highest value using the **VLOOKUP** function in Excel. In addition, we’ll also learn to obtain the next highest value with the **VLOOKUP** function.

**Table of Contents**hide

## Download Practice Workbook

## Introduction to VLOOKUP Function

In simple terms, the **VLOOKUP**, or **Vertical Lookup** function takes the user’s input, looks it up in the Excel worksheet, and returns an equivalent value related to the same input.

**Function Objective:**

**The VLOOKUP function looks for a value in the left-most column of a table, and then returns a value in the same row from a column you specify.**

**Syntax:**

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

**Argument Explanation:**

Argument | Required/Optional | Explanation |
---|---|---|

lookup_value | Required | value we want to lookup |

table_array | Required | range of cells containing input data |

col_index_num | Required | column number of the lookup value |

range_lookup | Optional | TRUE refers to approximate match, FALSE indicates exact match |

**Return Parameter:**

**Returns an exact or approximate value corresponding to the user’s input value.**

## 4 Ways to Return the Highest Value Using VLOOKUP Function in Excel

First and foremost, let’s consider the Employee Information dataset shown in the **B4:D14** cells, which shows the **Total Point**, **Sales Rep.**, and **ID** of the employees respectively. On this occasion, we want to return the highest value with the **VLOOKUP** function in Excel. Henceforth, without further delay, let’s glance at each method with the appropriate illustration.

Here, we have used the *Microsoft Excel 365* version; you may use any other version according to your convenience.

### 1. Return Highest Value in the Same Worksheet

To begin with, let’s see how we can return the highest value in the same worksheet using the **MAX **and **VLOOKUP **functions. Here, the **MAX function** returns the largest value in a given range while the **VLOOKUP** function looks up and returns the matched value.

📌 ** Steps**:

- Initially, go to the
**B17**cell >> enter the formula given below.

`=MAX(B5:B14)`

Here, the **B5:B14** cells refer to the *“Total Point”* column.

- Next, move to the
**C17**cell >> type in the expression below.

`=VLOOKUP(MAX($B$5:$B$14),B$5:D$14,2,FALSE)`

**Formula Breakdown:**

**MAX($B$5:$B$14) →**returns the largest value in a set of values. Here, the**$B$5:$B$14**cells is theargument which represents the*number1**“Total Point”*column.**Output → 99**

**VLOOKUP(MAX($B$5:$B$14),B$5:D$14,2,FALSE) →**looks for a value in the left-most column of a table, and then returns a value in the same row from a column you specify. Here,**MAX($B$5:$B$14)**(argument) is mapped from the*lookup_value***B$5:D$14**(argument) array. Next,*table_array***2**(argument) represents the column number of the lookup value. Lastly,*col_index_num***FALSE**(argument) refers to the*range_lookup***Exact match**of the lookup value.**Output → Luke**

- Eventually, navigate to the
**D17**cell >> insert the following equation.

`=VLOOKUP(MAX(B5:B14),B5:D14,3,FALSE)`

For instance, the **B4:B14** cells point to the *“Total Point”* column.

**Read More:** **VLOOKUP Max of Multiple Values (With Alternative)**

### 2. Retrieve Highest Value from Another Worksheet

Alternatively, the **VLOOKUP** function can look up a value from a different worksheet. So let’s see it in action.

📌 ** Steps**:

- First, enter the formula given below into the
**B5**cell.

`=MAX('Employee Info.'!B5:B14)`

In this case, *“Employee Info.”* is the name of the worksheet whereas the **B5:B14** cells represent the dataset.

- Second, move to the adjacent
**C5**cell >> enter the following equation.

`=VLOOKUP(MAX('Employee Info.'!B5:B14),'Employee Info.'!B5:D14,2,FALSE)`

In this scenario, the **B5:B14** cells represent the dataset, and the *“Employee Info.”* is the name of the worksheet.

- Third, proceed to the
**D5**cell >> apply the**VLOOKUP**function.

`=VLOOKUP(MAX('Employee Info.'!B5:B14),'Employee Info.'!B5:D14,3,FALSE)`

**Read More:** **VLOOKUP Max Value in Excel (With Limitations and Alternative Options)**

**Similar Readings**

**VLOOKUP Not Working (8 Reasons & Solutions)****Excel LOOKUP vs VLOOKUP: With 3 Examples****INDEX MATCH vs VLOOKUP Function (9 Examples)****Why VLOOKUP Returns #N/A When Match Exists? (5 Causes & Solutions)****Excel VLOOKUP to Return Multiple Values Vertically**

### 3. Fetch Highest Value from Multiple Worksheets

Conversely, we can also return the highest value from multiple worksheets using the **VLOOKUP** function. In this situation, let’s assume the **Employee Information for Asia Region **dataset which displays the **Total Point**, **Sales Rep**, and **ID **respectively.

Similarly, we have the **Employee Information for Europe Region **dataset.

Lastly, the dataset of **Employee Information for Africa Region **is available.

📌 ** Steps**:

- In the first place, navigate to the
**B5**cell >> insert the following expression into the**Formula Bar**.

`=MAX(Asia!B5:B11,Europe!B5:B11,Africa!B5:B11)`

Here, the **B5:B11** cells indicate the *“Total Point”* column in the *“Asia”*, *“Europe”*, and *“Africa”* worksheets.

- Afterward, enter the expression below into the
**C5**cell.

`=IFERROR(VLOOKUP(MAX(Asia!B5:B11),Asia!$B$5:$D$11,2,FALSE),IFERROR(VLOOKUP(MAX(Europe!B5:B11),Europe!$B$5:$D$11,2,FALSE),IFERROR(VLOOKUP(MAX(Africa!B5:B11),Africa!$B$5:$D$11,2,FALSE),"NOT FOUND")))`

**Formula Breakdown:**

**VLOOKUP(MAX(Asia!B5:B11),Asia!$B$5:$D$11,2,FALSE) →**here,**MAX(Asia!B5:B11)**(argument) is mapped from the*lookup_value***Asia!$B$5:$D$11**(argument) array in the*table_array**“Asia”*worksheet. Next,**2**(argument) represents the column number of the lookup value. Lastly,*col_index_num***FALSE**(argument) refers to the*range_lookup***Exact match**of the lookup value.**Output → Luke**

**VLOOKUP(MAX(Europe!B5:B11),Europe!$B$5:$D$11,2,FALSE) →**the**MAX(Europe!B5:B11)**(argument) is mapped from the*lookup_value***Europe!$B$5:$D$11**(argument) array in the*table_array**“Europe”*worksheet.**Output → Jon**

**VLOOKUP(MAX(Africa!B5:B11),Africa!$B$5:$D$11,2,FALSE) →**here,**MAX(Africa!B5:B11)**(argument) is mapped from the*lookup_value***Africa!$B$5:$D$11**(argument) array in the*table_array**“Africa”*worksheet.**Output → Chris**

**IFERROR(VLOOKUP(MAX(Asia!B5:B11),Asia!$B$5:$D$11,2,FALSE),IFERROR(VLOOKUP(MAX(Europe!B5:B11),Europe!$B$5:$D$11,2,FALSE),IFERROR(VLOOKUP(MAX(Africa!B5:B11),Africa!$B$5:$D$11,2,FALSE),”NOT FOUND”)))****→**becomes**IFERROR((“Luke”, “Jon”, “Chris”),”NOT FOUND”)****→**the**IFERROR function**returns value_if_error if the error has an error and the value of the expression itself otherwise. Here, the**(“Luke”, “Jon”, “Chris”)**is theargument, and*value***“NOT FOUND”**is theargument. In this case, the function returns the name corresponding to the*value_if_error**“Highest Point”*.**Output → Luke**

- Similarly, copy and paste the formula into the
**D5**cell to get the employee*“ID”*corresponding to*“Luke”*which is*“104”*.

`=IFERROR(VLOOKUP(MAX(Asia!B5:B11),Asia!$B$5:$D$11,3,FALSE),IFERROR(VLOOKUP(MAX(Europe!B5:B11),Europe!$B$5:$D$11,3,FALSE),IFERROR(VLOOKUP(MAX(Africa!B5:B11),Africa!$B$5:$D$11,3,FALSE),"NOT FOUND")))`

### 4. Yield Highest Value Based on Criteria

For one thing, we can compute the highest value using the **VLOOKUP** function based on specific criteria. Here, we’ll combine the **MAXIFS function** returns the highest value according to the criteria, and the **VLOOKUP** function, which retrieves the matched value.

Now, suppose, we have the **Sales Data of Stallion Corporation** dataset shown in the **B4:E17** cells, which depicts the *“Employee ID”*, *“Employee Name”*, *“Salary”*, and *“Joining Date”*.

📌 ** Steps**:

- To start with, proceed to the
**D19**cell >> enter the formula given below.

`=VLOOKUP(MAXIFS(B5:B17,D5:D17,">10000"),B5:E17,2,FALSE)`

**Formula Breakdown:**

**MAXIFS(B5:B17,D5:D17,”>10000″) →**returns the maximum value among cells specific by a given set of criteria. Here,**B5:B17**(argument) from where the value is returned. Next, the*max_range***D5:D17**(argument) from where the value matching the*criteria_range***“>10000”**(argument) is matched.*criteria1***Output → 135**

**VLOOKUP(MAXIFS(B5:B17,D5:D17,”>10000″),B5:E17,2,FALSE) →**becomes**VLOOKUP(135,B5:E17,2,FALSE) →**Here,**135**(argument) is mapped from the*lookup_value***B5:E17**(argument) array. Next,*table_array***2**(argument) represents the column number of the lookup value. Lastly,*col_index_num***FALSE**(argument) refers to the*range_lookup***Exact match**of the lookup value.**Output → Gloria**

## Alternative of VLOOKUP Function: Using INDEX-MATCH Formula to Get Highest Value

Now, if you do not wish to use the **VLOOKUP** function for whatever reason, you can utilize the **INDEX** and **MATCH** functions to return the highest value, so just follow along.

📌 ** Steps**:

- In the first place, enter the
**D19**cell and apply the following equation.

`=INDEX(B5:E17,MATCH(MAX(D5:D17),D5:D17,0),2)`

**Formula Breakdown:**

**MAX(D5:D17) →**for instance, the**$B$5:$B$14**cells is theargument which represents the*number1**“Total Point”*column.**Output → $45,000**

**MATCH(MAX(D5:D17),D5:D17,0)→**In this formula, the**MAX(D5:D17)**cell points to the*“Salary”*of*“$45,000”*. Next,**D5:D17**represents the array from which the*“Salary”*column where value is matched. Finally,**0**indicates the**Exact match**criteria.**Output → 9**

**INDEX(B5:E17,MATCH(MAX(D5:D17),D5:D17,0),2) →**becomes**INDEX(B5:E17,9,2) →**returns a value at the intersection of a row and column in a given range. In this expression, the**B5:E17**is theargument which is the marks scored by the students. Next,*array***9**is theargument that indicates the row location. Lastly,*row_num***2**is the optionalargument that points to the column location.*column_num***Output → Natasha**

## How to Obtain the Next Highest Value with VLOOKUP

Furthermore, we can fetch the second-highest value using the **VLOOKUP **function. It’s simple and easy, so just follow the steps shown below.

📌 ** Steps**:

- First of all, jump to the
**D19**cell >> type in the formula below.

`=VLOOKUP(MAX(D5:D17),B5:E17,2,TRUE)`

For example, the **D5:D17** cells point to the *“Salary”* column.

**Read More:** **What Is a Table Array in VLOOKUP? (Explained with Examples)**

## Things to Remember

- First, the
**VLOOKUP**function always lookup for values from the leftmost top column to the right which means this function “*Never*” looks for the data on the left. - Second, if we enter a value less than “
*1*” as the column index number, the function will return an error. - Third, if there are multiple highest values in a worksheet, then the
**VLOOKUP**function returns the first highest value in the list.

## Practice Section

We have provided a** Practice** section on the right side of each sheet so you can practice yourself. Please make sure to do it by yourself.

## Conclusion

In essence, this article shows 4 effective methods on how to **VLOOKUP** highest value. So, read the full article carefully and download the free workbook to practice. Now, we hope you find this article helpful, and if you have any further queries or recommendations, please feel free to comment here. Lastly, visit **ExcelDemy** for many more articles like this.