How to Return the Highest Value Using VLOOKUP in Excel

apply the vlookup function

The VLOOKUP function is one of the most useful and at the same time one of the most sophisticated functions in Excel. This function not only helps you to assemble your data from different worksheets but also helps you to find the highest value in a worksheet. Today, in this article we will discuss how to return the highest value using the VLOOKUP function.

Download Practice Workbook

Download this practice sheet to practice while you are reading this article.

VLOOKUP in Excel: Definition

The “VLOOKUP” or “Vertical Lookup” function takes the user’s input, looks up for it in the Excel worksheet, and returns an equivalent value related to the same input. This function is available in all versions of Excel from Excel 2007.

Summary

The “VLOOKUP” function takes the input value, searches it in the worksheets, and returns the value matching the input.

Syntax

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

Argument

  • Lookup_value: The value we want to find by searching it in another worksheet (Required)
  • table_array: The range of cells in another worksheet containing out input data (Required)
  • Col_index_num: The specific column number in the sheet_range containing the information we want to achieve (Required)
  • [range_lookup]: value is either TRUE or FALSE (optional)
    • if TRUE, returns either an exact or approximate match.
    • if FALSE, VLOOKUP will find an exact match.

Return Value

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

VLOOKUP to Return the Highest Value: 3 Approaches

We can find out the highest value in a worksheet using the combination of the “VLOOKUP” and the MAX function. We will now discuss three different approaches to return the highest value using “VLOOKUP”

1. Using VLOOKUP to Return Highest Value in the Same Worksheet

Step-1:

Consider a worksheet where “ID”, “Region” . and “Total Point” of some “Sales Rep”. are given. We have to find the ID and Name of the Sales Rep who has the highest point.

creating table

Step-2:

Make three columns named “Highest point”, “ID”, and “Sales Rep.” anywhere in the worksheet.

new table

Step-3:

First, we will find the “Highest Point” by using the “MAX” function.

=MAX(B4:B13)

Where (B4:B13) is the number range.

apply max function

Press “Enter”.

result

Step-4:

Now we will use the combination of the “VLOOKUP” and the “MAX” function to find the “Sales Rep.” name with the highest value. To do it, Apply the VLOOKUP and the MAX function. The final form of this formula is

=VLOOKUP(MAX(B4:B13),B4:E13,2,FALSE)

Where,

  • Lookup_value is the highest number MAX(B4:B13).
  • table_array is B$4:E$13.
  • Col_index_num is
  • [range_lookup]: we want the exact match (FALSE)

apply the vlookup function

Press “Enter”. We have the Name who has the highest point.

result

Step-5:

Similarly, we can find the ID number using the same formula. You just have to change the column index number from 2 to 3.

final result

2. Using VLOOKUP to Return Highest Value from Another Worksheet in the Same Workbook

In some cases, there might be a possibility where we have to return the highest value from another worksheet using the VLOOKUP function. We will now discuss it.

Step-1:

Make a table containing the “Highest point”, “Name”, and “ID” columns in a different worksheet.

creating table

Step-2:

Use the MAX function to bring the highest number from the “M1” sheet.

max value

Step-3:

Apply the VLOOKUP with MAX formula. The final formula of this method is this

=VLOOKUP(MAX(‘M1′!B4:B13),’M1’!B4:E13,2,FALSE)

Where,

  • Lookup_value is the highest number MAX(‘M1’!B4:B13). Click on the M1 sheet to go there and select the range.
  • table_array: is ‘M1’!B4:E13
  • Col_index_num is
  • [range_lookup]: we want the exact match (FALSE).

vlookup with max

Press “Enter”. The result is achieved.

result

We can return the ID with the highest value by using the same formula. Just change the column index number from 2 to 3.

final result

3. Using VLOOKUP to Return Highest Value from Multiple Worksheets Using IFERROR

In this method, we will learn how to return the highest value from multiple worksheets using the VLOOKUP with the “IFERROR” function.

Step-1:

First, we will create three new worksheets where we will input some information. Follow these screenshots.

For worksheet named “Asia”

making worksheet

For worksheet named “Europe”.

making worksheet

For Worksheet named “Africa”

making worksheet

Step-2:

Now we will create another worksheet where we want to return the highest value.

creating table

Step-3:

Apply the MAX function to find the highest value. Here we will use those three worksheets combined. Follow this formula.

=MAX(Asia!B4:B13,Europe!B4:B8,Africa!B4:B8)

Here B4:B13 is the cell range of the “Asia”, “Europe”, and “Africa” worksheets.

applying max formula

Step-4: Now we will apply the combination of the “VLOOKUP”, the “MAX”, and the “IFERROR” function to return the highest value.

The format for this formula is,

=IFERROR(VLOOKUP(MAX(..)…), IFERROR(VLOOKUP(MAX(..)…), …, “Not found”))

Now insert out data in this formula. The final look of the formula is,

=IFERROR(VLOOKUP(MAX(Asia!B4:B13),Asia!$B$4:$E$13,2,FALSE),IFERROR(VLOOKUP(MAX(Europe!B4:B8),Europe!$B$4:$E$8,2,FALSE),IFERROR(VLOOKUP(MAX(Africa!B4:B8),Africa!$B$4:$E$8,2,FALSE),”NOT FOUND”)))

Where,

  • Lookup_value is the highest number in the three worksheets MAX(Asia!B4:B13),(Europe!B4:B8),(Africa!B4:B8).
  • table_array: is Asia!$B$4:$E$13,Europe!$B$4:$E$8,Africa!$B$4:$E$8.
  • Col_index_num is 2
  • [range_lookup]: we want the exact match (FALSE)
  • If value is not available, then type “NOT FOUND”

using the vlookup with iferror

Press “Enter”. We have got our highest value.

result

Step-5:

We can return the ID for the highest value in the same way.

final result

Things to Remember

➤ The VLOOKUP function always searches for lookup values from the leftmost top column to the right. This function “Never” searches for the data on the left.

➤If you enter a  value less than “1” as the column index number, the function will return an error.

➤ If there is a multiple highest value in a worksheet, the “VLOOKUP” will only return the highest value that comes first in the list.

Conclusion

Returning the highest value in the same worksheet or different worksheets using the VLOOKUP function is discussed in this article. Though this function is difficult for the new users to comprehend, we tried to make it as simple as possible. Hope this article is useful for you. Share your thoughts if you have any confusion.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo