Bishawajit Chakraborty
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.
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”.
- 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)- 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
- 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 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)
- 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.
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)
- 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.
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.
