Summary

The Excel LARGE function returns a numeric value based on its position in a list when sorted by value in descending order. In other words, LARGE can retrieve the "nth largest" value – 1st largest value, 2nd largest value, 3rd largest value, etc.

Purpose 

Get nth largest value

Return value 

The nth largest value in an array.

Syntax

=LARGE(array,k)
  • array - An array or range of numeric values.
  • k - Position as an integer, where 1 corresponds to the largest value.

How to use 

The LARGE function returns a numeric value based on its position in a list when sorted by value. In other words, LARGE returns the "nth largest" value in the list where 1 corresponds to the largest value, 2 corresponds to the second largest value, etc. For example, the LARGE function is useful when you want to retrieve first, second, or third highest scores for a test. 

The LARGE function takes two arguments, array and k. Array is an array or range of numeric values. The argument k represents position or rank. For example, to return the largest value in array, provide 1 for k. To return the fifth largest value in array, provide 5 for k.

To get nth smallest values, see the SMALL function.

Examples

In the formula below, the LARGE function returns the third largest value in a list of five numbers provided in an array constant:

=LARGE({29,14,33,19,17},3) // returns 19

Note values do not need to be sorted. To retrieve the 1st, 2nd, and 3rd largest values in a range:

=LARGE(range,1) // 1st largest value
=LARGE(range,2) // 2nd largest value
=LARGE(range,3) // 3rd largest value

In the example shown, the formulas in G5, G6, and G7 are, respectively:

=LARGE(D5:D16,1) // returns 92
=LARGE(D5:D16,2) // returns 89
=LARGE(D5:D16,3) // returns 86

See below for more advanced formulas based on the LARGE function.

Notes

  • LARGE ignores empty cells, text values, and TRUE and FALSE values.
  • If array contains no numeric values, LARGE returns a #NUM! error.
  • To determine the rank of a number in a data set, use the RANK function.
Dave Bruns Profile Picture

AuthorMicrosoft Most Valuable Professional Award

Dave Bruns

Hi - I'm Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.