How to Use LARGE Function in Excel (7 Easy Examples)

Get FREE Advanced Excel Exercises with Solutions!

In this article, you will learn about the Excel LARGE function and how to use the LARGE function with criteria, duplicates, multiple criteria, text, and with the VLOOKUP function. You will also explore how you can find the largest value or second largest value based on criteria.

When we need to find a specific largest value, like the 2nd or the 3rd biggest number in any dataset. Excel’s LARGE function returns numeric values based on their position in a list when sorted by value. This article will share the idea of how the LARGE function works in Excel autonomously and then with other Excel functions. After finishing this article, you will know almost everything there is to know about the LARGE function in Excel.


Overview of Excel LARGE Function:

In the following picture, you can see the overview of the LARGE function.

Overview of Excel LARGE Function


Introduction to Excel LARGE Function

  • Summary

Returns the K-th largest value in a dataset where K must be a positive integer.

  • Syntax
=LARGE(array, k)
  • Arguments
ARGUMENT REQUIREMENT DESCRIPTION
array Required The array from which you need to choose the kth largest value.
k Required Pass an integer that specifies the position from the largest value as the nth position.

Note: 

  • Here the value of K should be greater than 0. (K>0)
  • LARGE(array,1) returns the biggest value and LARGE(array,n) returns the smallest value if n is the number of data points in a range.
  • The LARGE function is suitable for numeric values only. It ignores the text, blank cells, and logical values.

LARGE Function in Excel: 7 Examples

Now, In this section, I will give 7 examples that will help you understand the application of this function in detail. So, let’s get started with our first example.

1. Use of LARGE Function to Get Top N Values in Excel

Let’s have a dataset of some students with their Name, Department, Admission Date, Graduation Date, and CGPA. From this dataset, we will find out the top 3 results using the LARGE function.

Use of LARGE Function to Get Top N Values in Excel

To do that, follow the steps below.

Steps:

  • Enter the formula in cell E16 and copy it down up to the E18 cell.
=LARGE($F$5:$F$12, D16)

Use of LARGE Function to Get Top N Values in Excel

How Does the Formula Work?

  • LARGE($F$5:$F$12, D16)

Here, $F$5:$F$12 is the range where the LARGE function will search values. In this cell D16, we have passed the position of the searching elements.

Read More: How to Find Largest Number in Excel


2. Combining AVERAGE & LARGE Functions in Excel to Average Largest N-Values

For this example, let’s assume we need to find out the average CGPA of the top 4 students’ GPAs.

Determining the Average of Top 4 student's GPA using LARGE & AVERAGE functions

We can find this using Excel’s LARGE and AVERAGE functions. Follow the steps below.

Steps:

  • Enter the following formula in cell D15.
=AVERAGE(LARGE(F5:F12, {1,2,3,4}))

Determining the Average of Top 4 student's GPA using LARGE & AVERAGE functions

How Does the Formula Work?

  • LARGE(F4:F11, {1,2,3,4})

This portion will find the top 4 largest values from the CGPA dataset. {1,2,3,4} this is used to define the top 4 values using an array argument.

  • AVERAGE(LARGE(F5:F12, {1,2,3,4}))

The AVERAGE function calculates the average of the selected values.


3. Combining SUM & LARGE Functions in Excel to SUM Largest N-Values

For this example, let’s assume we need to find out the sum of the top 4 students’ GPAs.

Determining the sum of Top 4 student's GPA using LARGE & SUM functions

We can find this using Excel’s LARGE SUM functions. Follow the steps below.

Steps:

  • And enter the following formula in E15.
=SUM(LARGE(F5:F12, {1,2,3,4}))

Determining the sum of Top 4 student's GPA using LARGE & SUM functions

How Does the Formula Work?

  • LARGE(F4:F11, {1,2,3,4})

This portion will find the top 4 largest values from the CGPA dataset. {1,2,3,4} this is used to define the top 4 values using an array argument.

  • SUM(LARGE(F5:F12, {1,2,3,4}))

The SUM function returns the summation.


4. Utilizing INDEX, MATCH & LARGE Functions in Excel to Get Associated Data

By default, we can just only extract the numerical value using the LARGE function. But sometimes we may need to find the associated data with the largest value in the nth position. In this section, we will see how to find the top 3 student names with the help of combining the LARGE function with theINDEX & MATCH functions.

Utilizing INDEX, MATCH & LARGE Functions to Get Associated Data

To learn more, follow the steps below.

Steps:

  • Enter the formula in cell E16 and copy it down to E18. You will get the following result.
=INDEX($B$5:$B$12, MATCH(LARGE($F$5:$F$12, $D16), $F$5:$F$12, 0))

Utilizing INDEX, MATCH & LARGE Functions to Get Associated Data

How Does the Formula Work?

  • LARGE($F$5:$F$12, $D16)

This portion of the formula finds the highest (D16=1) CGPA in the F5:F12 range.

  • MATCH(LARGE($F$5:$F$12, $D16), $F$5:$F$12, 0)

This portion of the formula provides the row number of the top CGPA holder in the F5:F12 column.

  • INDEX($B$5:$B$12, MATCH(LARGE($F$5:$F$12, $D16), $F$5:$F$12, 0))

Lastly, the INDEX function will return the associated data with the largest value from $B$5:$B$12 column.

Read More: How to Lookup Next Largest Value in Excel


5. Combining ROWS & LARGE Functions in Excel to Sort Numbers in Descending Order

Let’s think we need to sort the CGPA of the students in a separate column (Sorted CGPA).

We can do this using ROWS and LARGE functions easily. Let’s follow the steps below.

Steps:

  • Enter the formula H5 and copy it down to H12.
=LARGE($F$5:$F$12, ROWS(F$5:F5))

Application of ROWS & LARGE Functions to Sort Numbers in Descending Order

How Does the Formula Work?

  • ROWS(F$5:F5)

This part of the formula returns the rows number of rows in the range.

  • LARGE($F$5:$F$12, ROWS(F$5:F5))

Lastly, the LARGE function finds all the large numbers according to the row serial from this $F$5:$F$12 range.


6. Use of LARGE Function to Find Nearest Dates

Using the LARGE and ROWS functions, we can find the recent dates. Let’s say we want to find the most recent 3 student’s admission dates.

Use of LARGE Function to Find Nearest Last Date

To accomplish that, follow the steps below.

Steps:

  • Enter the formula in cell D15 and copy it down up to D17 cell.
=LARGE($D$5:$D$12, ROWS(D$5:D5))

Use of LARGE Function to Find Nearest Last Date

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


7. Use the LARGE Function to Get an Upcoming Date Nearest to Today or a Specified Date

Now, in this section, we find out the upcoming 3 graduation dates. For me, today is 9th November 2022. Now we will find the top 3 three dates which are near to the current date.

To do that, follow the steps below.

Steps:

  • Enter the following three formulas in cells D16, D17, and D18 respectively.
=LARGE($E$5:$E$12, COUNTIF($E$5:$E$12, ">"&TODAY()))

And,

=LARGE($E$5:$E$12, COUNTIF($E$5:$E$12, ">"&TODAY())-1)

And,

=LARGE($E$5:$E$12, COUNTIF($E$5:$E$12, ">"&TODAY())-2)

Using the LARGE Function to Get a Future Date Closest to Today or a Particularized Date

How Does the Formula Work?

  • COUNTIF($E$5:$E$12, “>”&TODAY())

This part will count the number of cells using the condition. The condition is the date must be greater than today. Today’s date is found using the TODAY function. To know more about TODAY and COUNTIF functions, you can check these two articles:

  • LARGE($E$5:$E$12, COUNTIF($E$5:$E$12, “>”&TODAY()))

Finally, the LARGE function is used to find the largest dates.


When Will LARGE Function Not Work in Excel?

This LARGE function will not work for the following circumstances:

  • If the k value is a negative number.
  • If the k value is higher than the number of values in an array.
  • The provided array is empty or does not include a single numeric value.

Things to Remember

COMMON ERRORS WHEN THEY ARE SHOWN
#NUM! This error will appear if the array is empty. Also if k ≤ 0 or if k is greater than the number of data points.
#VALUE! This error will appear if the supplied K is a non-numeric value.

Frequently Asked Questions

1. Are there any alternative functions to the LARGE function? 

Yes, if you want to find the largest value in a range without specifying a position, you can use the MAX function.

2. Can the LARGE function handle an array that contains duplicate values? 

Yes, the LARGE function can handle arrays with duplicate values. If there are duplicate values in the array, the function will consider them as separate values and retrieve the kth largest value accordingly.

3. Can I use the LARGE function with a range of cells in a different worksheet? 

Yes, you can use the LARGE function with a range of cells in a different worksheet by specifying the worksheet name along with the cell range. For example, you can use ‘Sheet2!A1:A10‘ to refer to cells A1 to A10 in Sheet2.


Download Practice Workbook


Conclusion

That’s it all about LARGE function. Here, I have tried to give a piece of proper knowledge about this function and its different applications. I have shown multiple cases with their respective examples, but there can be many other iterations depending on numerous situations. Please let us know in the comment section if you have any inquiries or feedback.


Excel LARGE Function: Knowledge Hub


<< Go Back to 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. Abdullah Al Murad
Md. Abdullah Al Murad

Md. Abdullah Al Murad is a computer engineer who loves exploring Excel and VBA programming. Programming, to him, is a time-saving tool for efficiently managing data, files, and internet tasks. He is proficient in C, C++, Python, JavaScript, HTML, SQL, MySQL, PostgreSQL, Microsoft Office, and VBA and goes beyond the basics. He holds a B.Sc. in Computer Science and Engineering from American International University-Bangladesh. He has transitioned into the role of a content developer. Beyond his work, find... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo