In this article, we will demonstrate 3 handy ways to find the largest lookup value using the combination of **the VLOOKUP** **function** with other functions in Excel.

**Table of Contents**hide

## Download Practice Workbook

You may download the following Excel workbook for better understanding and practice it by yourself.

## 3 Easy Ways to Find the Largest Lookup Value in Excel

This article will demonstrate how to find the largest lookup value in Excel by combining the **MAX **and **VLOOKUP **functions, nesting **VLOOKUP **and **MAXIFS **functions with specific criteria, and merging * INDEX*,

*, and*

**MATCH****MATCH**functions. Let’s suppose we have a sample data set.

### Method 1: Combining MAX and VLOOKUP Functions to Lookup Largest Value in Excel

Let’s see how we can return the largest lookup value using the **MAX **and **VLOOKUP **functions. Here,** the MAX function **returns the largest value in a given range while

**looks up and returns the matched value**

*the VLOOKUP function***Steps:**

- Firstly, go to the
**B17**cell. - Secondly, enter the formula given below.

`=MAX(B5:B14)`

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

- Next, move to the
**C17**cell. - After that, 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”***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, go to the
**D17**cell. - Therefore, insert the following equation.

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

- For example, the
**B4:B14**cells point to the*“Total Point”.*

**Read More: ****How to Lookup Next Largest Value in Excel (4 Easy Ways)**

### Method 2: Nesting VLOOKUP and MAXIFS Functions to Lookup Largest Value Based on Criteria

Here, we can calculate the highest value using ** the VLOOKUP function** based on specific criteria. But, we’ll combine the

**MAXIFS function**with

**which returns the highest value according to the specific criteria.**

*the VLOOKUP function***Steps:**

- Now, suppose, we have created another dataset, which shows the
*“Employee ID”*,*“Employee Name”*,*“Salary”*, and*“Joining Date”*. - Firstly, choose the
**D19**cell. - Then, 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**

- Finally, you will get the results here in the below image.

**Read More: ****How to Find Second Largest Value with Criteria In Excel**

### Method 3: Merging INDEX, MATCH, and MAX Functions to Lookup Largest Value in Excel

So, we will demonstrate another method of merging the **INDEX**, **MATCH**, and **MAX **functions to find the largest lookup value.

**Steps:**

- Firstly, go to the
**D19**cell and apply the following formula.

`=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”***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****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**

- Therefore, you will get the results here in the below image.

**Read More: ****How to Use Excel Large Function with Criteria( 4 Suitable Ways)**

## Conclusion

In this article, we’ve covered 3 ways to find the largest lookup value in Excel. We sincerely hope you enjoyed and learned a lot from this article. Additionally, if you want to read more articles on Excel, you may visit our website, **Exceldemy**. If you have any questions, comments, or recommendations, kindly leave them in the comment section below.