Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

How to Find Largest Lookup Value in Excel (3 Easy Ways)

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.


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, MATCH, and MATCH functions. Let’s suppose we have a sample data set.

Easy Ways to Find the Largest Lookup Value in Excel


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 the VLOOKUP function looks up and returns the matched value

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

Showing Results for Combining  MAX and VLOOKUP Functions to Lookup Largest Value in Excel

  • 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 the number1 argument which represents the “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) ( 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

Showing Results for Combining  MAX and VLOOKUP Functions to Lookup Largest Value

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

Showing Results for Combining MAX and VLOOKUP Functions

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 the VLOOKUP function which returns the highest value according to the specific criteria.

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)

Nesting VLOOKUP and MAXIFS Functions to Lookup Largest Value Based on Criteria

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
  • Finally, you will get the results here in the below image.

Showing Results for Nesting VLOOKUP and MAXIFS Functions

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)

Merging INDEX, MATCH, and MAX Functions to Lookup Largest Value in Excel

Formula Breakdown
  • MAX(D5:D17) → for instance, the $B$5:$B$14 cells is the number1 argument which represents the “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 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
  • Therefore, you will get the results here in the below image.

Showing Results for Merging INDEX, MATCH, and MAX Functions

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.


Related Articles

Bishawajit Chakraborty

Bishawajit Chakraborty

I'm Bishawajit Chakraborty. Hello. I graduated from Rajshahi University of Engineering & Technology (RUET) with a degree in Mechanical Engineering. I'm working with ExcelDemy as a Content Developer for Excel & VBA. You can visit our website, Exceldemy if you'd like to read my published articles on MS Excel and VBA.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo