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.


In the formula below, the LARGE function returns the third largest value in a list of five numbers provided in an array constant: Note values do not need to be sorted. To retrieve the 1st, 2nd, and 3rd largest values in a range: In the example shown, the formulas in G5, G6, and G7 are, respectively: See below for more advanced formulas based on the LARGE function.


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

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.