# How to Return the Highest Value Using VLOOKUP in Excel 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.

## 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. Step-2:

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

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

=MAX(B4:B13)

Where (B4:B13) is the number range. Press “Enter”. 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) Press “Enter”. We have the Name who has the highest point. 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. ### 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. Step-2:

Use the MAX function to bring the highest number from the “M1” sheet. 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). Press “Enter”. The result is achieved. We can return the ID with the highest value by using the same formula. Just change the column index number from 2 to 3. ### 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” For worksheet named “Europe”. For Worksheet named “Africa” Step-2:

Now we will create another worksheet where we want to return the highest value. 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. 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,

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

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) Press “Enter”. We have got our highest value. Step-5:

We can return the ID for the highest value in the same way. ## 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.  