When we need to find a specific largest value, like the 2nd or the 3rd biggest number in any dataset. The Excelâ€™s **LARGE **function returns numeric values based on their position in a list when sorted by value. This article will share the complete idea of how the **LARGE **function works in Excel autonomously and then with other Excel functions.

**Table of Contents**hide

## Download the Practice WorkBook

**COUNT Function in Excel (Quick View)**

**Excel LARGE Function: Syntax & Arguments**

**Summary**

Returns the K-th largest value in a dataset. Like the third-largest number.

**Syntax**

`=LARGE (`

`array`

`, `

`k`

`)`

**Arguments**

Argument |
Required or Optional |
Value |
---|---|---|

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)
- If n is the number of data points in a range, then
**LARGE(array,1)**returns the largest value, and LARGE(array,n) returns the smallest value. - The
**LARGE**function processes only numeric values. Blank cells, text, and logical values are ignored.

**How to Use the LARGE Function in Excel (6 Examples)**

**Example 1: How to get the top N values in Excel Using LARGE Function**

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.

**Step 1:** Enter the formula in cells **E16** and copy it down up to **E18**

`=LARGE($F$4:$F$11, D16)`

**Formula Explanation**

**$F$4:$F$11**this is the range where the LARGE function will search values.- In this cell
**D16**, we have passed the position of the searching elements. Letâ€™s 1 means it will fund the first largest values from the range.

**Example 2: How to sum or average the largest N values Using LARGE Function**

For this example, letâ€™s assume we need to find out the average CGPA of the 4 students and the total of the top 4 studentsâ€™ GPAs. We can find this using Excelâ€™s **LARGE**, **SUM, **and **AVERAGE **functions.

**Step 1:** Enter the formulas in cells **D15 **and **D18**

`=AVERAGE(LARGE(F4:F11, {1,2,3,4}))`

And

`=SUM(LARGE(F4:F11, {1,2,3,4}))`

**Formula Explanation**

**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 value using array argument.- The
**AVERAGE**function calculates the average of the selected values, and the**SUM**function returns the summation.

**Example 3: Get Data Associated with the N-th Largest Value Using LARGE Function**

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 help of the **LARGE **function.

**Step 1:** Enter the formula in cell **E16 **and copy it down up to **E18**

`=INDEX($B$4:$B$11, MATCH(LARGE($F$4:$F$11, $D16), $F$4:$F$11, 0))`

**Formula Explanation**

**MATCH(LARGE($F$4:$F$11, $D16), $F$4:$F$11, 0)**this portion of the formula finds the matched data for the N-th largest CGPA.**LARGE($F$4:$F$11, $D16)**this is ourÂ lookup_value,**$F$4:$F$11**this is our lookup_array, andÂ 0 is ourÂ match_type which will find the exact match. If you want to know more about the**MATCH**function you can visit thisÂ link- Lastly, the
**INDEX**function will find the associated data with the largest values.**$B$4:$B$11**this is the range where we will find the associated data and the row will be selected using the**MATCH**and**LARGE**functions. If you want to know more about the**INDEX**function you can visit this link

**Example 4: LARGE Function Formula to Sort Numbers in Descending Order**

Letâ€™s think we need to sort the CGPA of the students in a separate column. We can do this using **ROWS **and **LARGE **functions easily.

**Step 1: **Enter the formula **C15 **and copy it down up to **C22**

`=LARGE($F$4:$F$11, ROWS(F$4:F4))`

**Formula Explanation**

**ROWS(F$4:F4)**this part of the formula returns the rows number of rows in the range. If you want to know more about the**ROWS**function you can check thisÂ link- Lastly, the
**LARGE**function finds all the large numbers according to the row serial from this**$F$4:$F$11**range.

**Example 5: LARGE Formula for Dates and Times**

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.

**Step 1:** Enter the formula in cell **D15 **and copy it down up to **D17**

`=LARGE($D$4:$D$11, ROWS(D$4:D4))`

**Example 6: Get a Future Date Closest to Today or a Particularized Date**

Now in this section, we find out the upcoming 3 graduation dates. For me, today is 12 August 2021. Now we will find the top 3 three dates which are near to may date.

**Step 1:** Enter the formula in cells **D16**, **D17**, and **D18**

`=LARGE($E$4:$E$11, COUNTIF($E$4:$E$11, ">"&TODAY()))`

And

`=LARGE($E$4:$E$11, COUNTIF($E$4:$E$11, ">"&TODAY())-1)`

And

`=LARGE($E$4:$E$11, COUNTIF($E$4:$E$11, ">"&TODAY())-2)`

**Formula Explanation**

**COUNTIF($E$4:$E$11, â€ś>â€ť&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:

How to Use COUNTIF Function in Excel (10 Suitable Applications)

How to Use TODAY Function in Excel (6 Easy Examples)**COUNTIF($E$4:$E$11, â€ś>â€ť&TODAY())-1**and**COUNTIF($E$4:$E$11, â€ś>â€ť&TODAY())-2**this used to find the second and third most upcoming dates.- Finally, the
**LARGE**function is used to find the largest dates.

**When LARGE Function will not Work**

This LARGE function will not 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 show |
---|---|

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

**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 methods with their respective examples but there can be many other iterations depending on numerous situations. If you have any inquiries or feedback, please let us know in the comment section.

## Further Readings

- How to use MAX function in Excel (6 Examples)
- How to use COUNTBLANK function in Excel (3 Examples)
- How to Use SMALL Function in Excel (4 Common Examples)
- How to Use MIN Function in Excel (5 Relevant Examples)
- How to Use COUNTIFS Function in Excel (4 Examples)
- How to Use COUNT Function in Excel (With 5 Examples)
- How to Use COUNTA Function in Excel (3 Suitable Examples)
- How to Use RANK Function in Excel (With 5 Examples)
- The Different Ways of Counting in Excel