How to Use LARGE Function with VLOOKUP Function in Excel

Get FREE Advanced Excel Exercises with Solutions!

Today we will discuss how we can use the LARGE function with the VLOOKUP function in Excel. The LARGE function returns the n-th largest value of a dataset, where the integer n represents the position of the biggest numeric value in the specified range of cells. However, only numeric values can be retrieved with the LARGE function. On the other hand, the VLOOKUP function looks at an array of data organized vertically and returns text or numeric values depending on the specified criteria by the user.

how to use large function in excel with vlookup


How to Use LARGE Function with VLOOKUP Function in Excel: 2 Suitable Methods

In this article, we will demonstrate with examples how we can use the LARGE function with the VLOOKUP function in Excel. We can combine LARGE and  VLOOKUP functions to extract the associated text data of a particular numeric value. We can also use LARGE, VLOOKUP, and ROWS functions to sort text data of descending numeric values.


1. Combine LARGE and VLOOKUP Functions to Extract Associated Text Data in Excel

We can use the LARGE function along with the VLOOKUP function in Excel to extract the associated text data of a numeric value. This method is very simple and easy to use. Here in this worksheet, we have the names of multiple students, their student IDs, their CGPAs (Cumulative Grade Point Average), their departments, and their permanent home addresses. We want to see the names of the top 3 students with the highest CGPAs.

Steps:

  • First, select the cell where you want to put the name of the student who has the highest CGPA. We are selecting cell E15.

Selecting a Particular Cell to Use LARGE Function with VLOOKUP Function in Excel

  • Enter the formula into the cell.
=VLOOKUP(LARGE($C$5:$C$11,D15),$C$5:$F$11,2,0)

Entering a Formula into the Cell

Formula Breakdown

  • LARGE($C$5:$C$11,D15) —> This portion of the formula finds the n-th largest value (n=D15) in range C5:C11. Here, D15=1, so it looks for the highest CGPA value.
  • VLOOKUP(LARGE($C$5:$C$11,D15),$C$5:$F$11,2,0)—> The VLOOKUP function finds the highest CGPA value in the range C5:C11. Then, it returns the exact match of the text data corresponding to the highest CGPA value from the 2nd column (Name) of the selected range (C5:F11).
  • Press Enter and you will see the name of the student with the highest CGPA. In this dataset, Ricky has the highest CGPA (3.79) among all the students. That’s why his name appeared in cell E15.

Extracting the Associated Text Data  by Using LARGE and VLOOKUP Functions

  • Now, use Fill Handle to autofill the formula in the range E16:E17.

Using Fill Handle to Autofill Data

  • Finally, you will see the names of the top 3 students with the highest CGPAs in the range E15:E17.

Combining LARGE and VLOOKUP Functions in Excel

Read More: How to Use Excel Large Function with Text


2. Nest LARGE, VLOOKUP, and ROWS Functions to Sort Text data in Descending Order in Excel

We can sort numeric values in descending order by combining the LARGE function with the ROWS function. Then, we can get the associated text data of those numbers with the use of the VLOOKUP function. Here the dataset is the same as in Method 1. The names of the students are not sorted according to their CGPAs. We will sort the names of the students in such a way the highest CGPA holder gets the top position and the lowest CGPA holder gets the bottommost position. In brief, we want to sort the names of the students according to their CGPAs in descending order.

Steps:

  • Select the cell from which you want to start the names from. We are selecting cell H5.

Selecting a Particular Cell to Use LARGE Function with VLOOKUP Function in Excel

  • Then, enter the formula into the cell.
=VLOOKUP(LARGE($C$5:$C$11,ROWS(C$5:$C5)),$C$5:$F$11,2,0)

Entering a Formula into the Cell

Formula Breakdown

  • ROWS(C$5:$C5)—> Returns the row number of the reference.
  • LARGE($C$5:$C$11,ROWS(C$5:$C5))—> Next, the LARGE function tries to find the largest number from range C5:C11 according to the descending row serial.
  • VLOOKUP(LARGE($C$5:$C$11,ROWS(C$5:$C5)),$C$5:$F$11,2,0)—> Finally, the VLOOUP function will return the exact match of the associated text data corresponding to the CGPA value from column no 2 (Name) of the selected range (C5:F11).
  • Next, press Enter and you will see the name of the student with the highest CGPA in cell H5.

Sorting the Associated Text Data  by Using LARGE, VLOOKUP, and ROWS Functions

  • Apply Fill Handle to Autofill data in range H6:H11.

Using Fill Handle to Autofill Data

  • Finally, you will see the names of the students in range H5:H11, according to their CGPAs in descending order.

Nesting LARGE, VLOOKUP, and ROWS Functions in Excel


Notes

There are a few limitations of the LARGE, and VLOOKUP functions. They are as follows:

  • The biggest drawback of the VLOOKUP function is it can retrieve data only from the columns that are situated on the right-hand side of the lookup value.
  • The LARGE function will not work if the second argument of the function (n-th value) is either a negative number or the assigned value is more than the number of rows of the specified range.
  • The LARGE function will not work if the selected array is empty or does not include any numeric value.

Read More: How to Use Excel LARGE Function with Criteria


Download Practice Book

You can download this practice book while going through this article.


Conclusion

In this article, we have talked in detail about how to use the LARGE function with the VLOOUP function in Excel. This article will allow users to use Excel more efficiently and effectively. If you have any questions regarding this essay, feel free to let us know in the comments. Also, if you want to see more Excel content like this, please visit our website and unlock a great resource for Excel-related content.


Related Articles


<< Go Back to Excel LARGE Function | Excel Functions | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Md. Abu Sina Ibne Albaruni
Md. Abu Sina Ibne Albaruni

Md. Abu Sina Ibne Albaruni holds a BSc in Mechanical Engineering from Bangladesh University of Engineering and Technology. He is a dedicated employee of the ExcelDemy project. He has written quite a large number of articles for ExcelDemy. Besides, he has expertise in VBA. He efficiently automates Excel issues using VBA macros and actively engages in the ExcelDemy forum, offering valuable solutions for user interface challenges. His areas of interest in work and study span MATLAB, Machine Learning,... Read Full Bio

2 Comments
  1. Thank you for explaining how to use the LARGE function with the VLOOKUP function in Excel. I find this information very helpful in my work. However, I am curious if there are any limitations to using these functions together? Can they be used with larger datasets or is there a point where they become inefficient? Thank you for your article and any additional information you can provide.

    • Reply Avatar photo
      Md. Abu Sina Ibne Albaruni Apr 4, 2023 at 11:23 AM

      Hi YOGAAGA

      Thank you very much for your comment. A few limitations while working with these functions have been discussed in the NOTES section. Please go through them. In addition, here in the dataset, the VLOOKUP function has been used in combination with the LARGE function. Both the VLOOPUP and LARGE functions go through the entire range of data every time they are called. The LARGE function needs to sort the data before determining the result. The VLOOKUP function looks for the data in a specified range. The whole process can be computationally expensive in large datasets. So, this formula can be time-consuming when dealing with large amounts of data.

      Moreover, this formula may return identical values if it encounters duplicates in the dataset. For example, in our dataset, if there are multiple students with the same CGPA, the formula will return the same student’s name for each duplicate value.

      Thank you once again. Let me know in the comments if you have any further queries.

      Regards,
      Md. Abu Sina Ibne Albaruni
      Team ExcelDemy

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo