How to Use LARGE Function with VLOOKUP Function in Excel

This guide will explain how to use the LARGE function with the VLOOKUP function in Excel to retrieve a specific value.

When we want to return a text value represented by the largest numeric value, we can do this by combining the LARGE function with the VLOOKUP function. 

The rules for using the LARGE function in Google Sheets are the following:

• The LARGE function is used to retrieve a numerical value corresponding to its rank in a list that is sorted in descending order by value.
• The LARGE function ignores any empty cells, text values, and TRUE and FALSE values.
• When the array has no numeric values, the LARGE function returns a #NUM! error. 

Excel offers several built-in functions that allow us to easily perform difficult tasks. Furthermore, we can combine multiple functions to create customized formulas for specific situations. 

We often need to extract specific data from a list of values.  When it comes to retrieving data based on criteria from an array of data, the VLOOKUP function works the best. 

In addition, we can utilize the LARGE function which retrieves the k-th largest value of an array of data. The integer k represents the position of the largest numeric value in the array, However, this function only returns numeric values.

Using the LARGE function and VLOOKUP function together allows us to retrieve text values depending on the specific k-th value they rank in the data set. 

In this guide, we will provide a step-by-step tutorial on how to use the LARGE function with the VLOOKUP function in Excel. Additionally, we will explore the syntax and a real example of using the function.

Great! Let’s dive right in.

The Anatomy of the LARGE Function

The syntax or the way we write the LARGE function is as follows:

=LARGE(array,k)

• = the equal sign is how we start any function in Excel.
• LARGE() is our LARGE function. This function returns the k-th largest value in a data set. For instance, the third largest number.
• array is a required argument. This refers to the array or range of data for which we want to determine the k-th largest value.
• k is another required argument. This is the position (from the largest) in the array or cell range of the value to return.

The Anatomy of the VLOOKUP Function

The syntax or the way we write the VLOOKUP function is as follows:

=VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup])

• = the equal sign is how we activate any function in Excel.
• VLOOKUP() refers to our VLOOKUP function. This function is used to return a value from the same row of the lookup value from a column we specify. 
• lookup_value is a required argument. This refers to the value found in the data set’s first column. Additionally, this value can be a value, a cell reference, or a text string.
• table_array is another required argument. This refers to a table of text, numbers, or logical values in which data is retrieved. 
• col_index_num is also a required argument. This refers to the column number in the table_array from which the matching value should be returned. 
• range_lookup is an optional argument. This is a logical value that defines the type of match to look for. If we want the exact match, we input FALSE or 0. If we want the closest match, we input TRUE or 1.

A Real Example of Using LARGE Function with VLOOKUP Function in Excel

Let’s say we have a data set containing the list of employees in the sales department. There are several pieces of information in the data set that helps us perform our task. 

Our initial data set would look like this:

In the spreadsheet above, we can see the Employee ID, Total Monthly Sales, Employee Name, and Department. We want to retrieve the names of the top 3 employees with the highest total monthly sales. 

The LARGE function will find the k-th largest value in the data set. Then, the  VLOOKUP function will find the highest total monthly sales from the selected range and return the matching name. 

We will be using this formula to perform this task:

=VLOOKUP(LARGE(C2:C7,G3),C2:E7,2,0)

Firstly, our lookup_value is the first portion of the formula, LARGE(C2:C7,G3), that finds the k-th largest value in the data set. In this case, we are looking for the highest total monthly sales since we selected G3=1.

Next, we will input the table_array by selecting all the columns starting from the total monthly sales column to the rightmost column (C2:E7).

Then, we will input our col_index_num value which is 2.

The col_index_num value refers to the column containing the value we want to extract. We start counting from the first column inputted in the table_array argument. We want to return the employee name which is the second column in our table_array. 

Lastly, we will input 0 at the end of our formula to ensure it will return the exact match.

Thus, our final data set would look like this:

You can make your own copy of the spreadsheet above using the link below. 

Amazing! Now we can dive into the steps of using the LARGE function with the VLOOKUP function in Excel.

How to Use LARGE Function with VLOOKUP Function in Excel

1. First, we will create a table to input the retrieved values. Since we want to return the names of the top 3 employees, we will make a table like the one below:

2. We will start by returning the name of the top 1 employee. We will select cell H3.

3. Now we will type in the formula “=VLOOKUP(LARGE(C2:C7,G3),C2:E7,2,0)”. The first portion of our formula finds the largest value in cells C2:C7. Since G3=1, it looks for the highest Total Monthly Sales value.

The rest of the formula finds the highest Total Monthly Sales value in the range C2:C7. Then, it returns the exact match of the text value corresponding to the highest Total Monthly Sales value from column 2, which is Employee Name.

4. Press the Enter key to return the result.

5. Drag down the Fill Handle tool to copy the formula and return the remaining top 2 and top 3 employees.

6. Lastly, press the Enter key to display all the results.

And tada! We have successfully used the LARGE function with the VLOOKUP function in Excel.

You can apply this guide whenever you need to extract a text value depending on the k-th largest value in the data set. You can now use the LARGE function and the various other Microsoft Excel formulas available to create great worksheets that work for you. 

FAQs:

1. Why can’t we use the LARGE function alone to retrieve data?

You can utilize the LARGE function alone to retrieve the k-th largest value of a data set. However, you can only retrieve numeric values. 

2. Can I also retrieve the k-th smallest value of a data set?

You can retrieve the k-th smallest value of a data set using the SMALL function. 

3. Can I sort text data of descending numeric values?

Yes, you can by combining the LARGE, VLOOKUP, and ROWS function together. 

That’s pretty much it! Make sure to subscribe to our newsletter to be the first to know about the latest guides and tutorials from us.