Return the Highest Value Using VLOOKUP Function in Excel

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.


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.

vlookup highest value function syntax

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

dataset for vlookup highest value

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.

Return Highest Value in the Same Worksheet

  • 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 the number1 argument which represents the “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) ( lookup_value argument) is mapped from the B$5:D$14 (table_array argument) array. Next, 2 (col_index_num argument) represents the column number of the lookup value. Lastly, FALSE (range_lookup argument) refers to the Exact match of the lookup value.
    • Output → Luke

Using VLOOKUP and MAX functions

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

vlookup highest value Return Highest Value in the Same Worksheet

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.

Retrieve Highest Value from Another Worksheet

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

Applying VLOOKUP and MAX functions

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

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

Retrieve Highest Value from Another Worksheet

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


Similar Readings


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.

Dataset for Fetch Highest Value from Multiple Worksheets

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.

Using MAX function

  • 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) ( lookup_value argument) is mapped from the Asia!$B$5:$D$11(table_array argument) array in the “Asia” worksheet. Next, 2 (col_index_num argument) represents the column number of the lookup value. Lastly, FALSE (range_lookup argument) refers to the 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) ( lookup_value argument) is mapped from the Europe!$B$5:$D$11(table_array argument) array in the “Europe” worksheet.
    • Output → Jon
  • VLOOKUP(MAX(Africa!B5:B11),Africa!$B$5:$D$11,2,FALSE) → here, MAX(Africa!B5:B11) ( lookup_value argument) is mapped from the Africa!$B$5:$D$11(table_array argument) array in the “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 the value argument, and “NOT FOUND” is the value_if_error argument. In this case, the function returns the name corresponding to the “Highest Point”.
    • Output → Luke

Using IFERROR function

  • 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")))

Fetch Highest Value from Multiple Worksheets


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

Yield Highest Value Based on Criteria

📌 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 (max_range argument) from where the value is returned. Next, the D5:D17 (criteria_range argument) from where the  value matching the “>10000” (criteria1 argument) is matched.
    • Output → 135
  • VLOOKUP(MAXIFS(B5:B17,D5:D17,”>10000″),B5:E17,2,FALSE) → becomes
    • VLOOKUP(135,B5:E17,2,FALSE) → Here, 135 ( lookup_value argument) is mapped from the B5:E17 (table_array argument) array. Next, 2 (col_index_num argument) represents the column number of the lookup value. Lastly, FALSE (range_lookup argument) refers to the Exact match of the lookup value.
    • Output → Gloria

vlookup highest value using VLOOKUP and MAXIFS functions


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 the number1 argument which represents the “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 the array argument which is the marks scored by the students. Next, 9 is the row_num argument that indicates the row location. Lastly, 2 is the optional column_num argument that points to the column location.
    • Output → Natasha

Using INDEX-MATCH Formula to Get Highest Value


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.

How to Obtain the Next Highest Value with VLOOKUP

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.

Practice Section


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.


Further Readings

Asikul Himel
We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo