ExcelStatisticalIntermediate

LARGE

Returns the k-th largest value in a data set.

Read the syntaxReview worked examplesOpen the spreadsheet app
=LARGE(array, k)
Open Spreadsheet Explorer

This static page keeps the function indexed for search, while the spreadsheet app handles interactive exploration and saved formulas.

What the function does

Overview

Returns the k-th largest value in a data set.

The LARGE function extracts the k-th largest numeric value from an array or range, providing a method to rank data without needing to sort the source range manually. While MAX returns only the absolute highest value (equivalent to k=1), LARGE allows users to specify any rank, such as the 2nd, 3rd, or n-th highest. A critical technical behavior is that LARGE treats duplicate values as distinct positions; for instance, if the two highest numbers in a range are both 100, then LARGE(range, 1) and LARGE(range, 2) will both return 100. The function ignores text, logical values, and empty cells within the specified array. For best practices, users should ensure the 'k' argument is a positive integer; if a floating-point number is provided for 'k', Excel will truncate it to an integer. This function is frequently used in combination with INDEX and MATCH to perform 'left-lookups' based on ranked values.

Quick reference

Syntax

=LARGE(array, k)

Inputs

Arguments

arrayData range
Example: B2:B100
kThe position (1=1st largest, 2=2nd largest)
Example: 2

Formula patterns

Examples

1

Retrieve the Second Highest Sales Total

=LARGE(B2:B20, 2)
salesranking
2

Identify the Third Place Competition Score

=LARGE(C2:C50, 3)
statisticsperformance
3

Dynamic Rank Extraction using a Cell Reference

=LARGE(A2:A100, E1)
dynamicdashboards

Avoid these issues

Common Errors

1

#NUM!

Cause: The k argument is either less than or equal to zero, or k is greater than the total number of numeric entries in the provided array.

Fix: Use the COUNT function to determine how many numbers are in your range and ensure k does not exceed that count.

2

#VALUE!

Cause: The k argument provided is non-numeric, such as text or a logical value.

Fix: Check that the second argument is a valid number or a cell reference that evaluates to a number.

Platform support

Compatibility

Excel 2007+Excel-first

Source: Microsoft Support

Common questions

Frequently Asked Questions

Returns the k-th largest value in a data set.

array: Data range k: The position (1=1st largest, 2=2nd largest)

#NUM!: Use the COUNT function to determine how many numbers are in your range and ensure k does not exceed that count. #VALUE!: Check that the second argument is a valid number or a cell reference that evaluates to a number.