Does LARGE return only unique values, or does it count duplicates?

LARGE counts duplicates. If your array is {5000, 7200, 5000, 9500}, the sorted descending order is 9500, 7200, 5000, 5000—so LARGE(..., 3) returns 5000 (the first occurrence in rank order), not the next unique value.

How do I find the customer name with the k-th largest revenue?

Wrap LARGE in INDEX/MATCH: =INDEX(A2:A9, MATCH(LARGE(C2:C9, 1), C2:C9, 0)). This finds the k-th largest value, matches it in the revenue column, and returns the corresponding customer name.

Can LARGE work with negative numbers?

Yes. LARGE sorts all numbers—positive, negative, and zero—in descending order. So if your array is {-500, 100, -1000, 50}, LARGE(...,1) returns 100, and LARGE(...,4) returns -1000.

What's the difference between LARGE and RANK?

LARGE returns the actual value (e.g., the $5,000 revenue); RANK returns the position or rank number (e.g., 3rd place). Use LARGE when you need the value, RANK when you need the position.

LARGE function

LARGE returns the k-th largest value in an array, where k=1 is the maximum, k=2 is the second-largest, and so on.

=LARGE(array, k)

Generate a LARGE formula

Describe what you need. The generator will reach for LARGE where LARGE is the right tool, and tell you when it is not.

Arguments

How LARGE reads its arguments

Argument	Required	Description
array	Required	One or more cells or ranges containing numeric values; empty cells and text are ignored.
k	Required	A positive integer specifying which largest value to return (1 = max, 2 = second-max, etc.); must be ≤ array length or #NUM! error occurs.

Returns

Returns a single numeric value from the array, matching the data type of the input.

Availability

Excel: All · Google Sheets: Supported

Worked examples

1. Find the top revenue account

Customer	Plan	MRR
Acme Corp	Enterprise	5000
TechStart	Professional	2500
Global Inc	Enterprise	7200
LocalBiz	Basic	500
SmallShop	Professional	2800
MegaCorp	Enterprise	9500
StartupLab	Basic	450
TechNova	Professional	3200

=LARGE(C2:C9, 1)

Result: 9500

LARGE with k=1 returns the maximum value. MegaCorp's $9,500 MRR is the highest among all customers.

2. Identify the second-largest account to track market concentration

Customer	Plan	MRR
Acme Corp	Enterprise	5000
TechStart	Professional	2500
Global Inc	Enterprise	7200
LocalBiz	Basic	500
SmallShop	Professional	2800
MegaCorp	Enterprise	9500
StartupLab	Basic	450
TechNova	Professional	3200

=LARGE(C2:C9, 2)

Result: 7200

With k=2, this returns the second-largest value: $7,200 from Global Inc. Useful for comparing top-2 accounts or checking revenue concentration risk.

3. Find the minimum MRR threshold for top-3 enterprise accounts

Customer	Plan	MRR
Acme Corp	Enterprise	5000
TechStart	Professional	2500
Global Inc	Enterprise	7200
LocalBiz	Basic	500
SmallShop	Professional	2800
MegaCorp	Enterprise	9500
StartupLab	Basic	450
TechNova	Professional	3200

=LARGE(C2:C9, 3)

Result: 5000

With k=3, returns $5,000—the third-largest MRR. This shows that the top-3 accounts all have revenue at or above this threshold, helping identify high-value customer tiers.

Common errors

Which LARGE error are you seeing?

Error	Why it happens	How to fix it
#NUM!	k is zero, negative, or larger than the size of the array (e.g., asking for the 15th largest from only 8 values).	Ensure k is a positive integer and does not exceed the array's length. Verify the range includes all intended data.
#VALUE!	k is supplied as text, a formula error, or a non-integer (e.g., 'largest' instead of 1).	Check that k is a number or a cell reference that evaluates to a number. Use INT() if needed to convert a decimal.
#N/A	The array argument contains a broken reference, is empty, or includes IFERROR/IFNA that returns #N/A.	Verify the range is valid and populated with numeric data. Check for circular references or missing sheets.

Tips and when to use something else

Use SMALL(array, k) to find the k-th smallest value instead—it's the inverse operation.
LARGE ignores ties and duplicates; if two customers both have $5,000 MRR, they occupy the same position in the sorted order but LARGE returns the value only once.
Combine LARGE with INDEX and MATCH to return not just the revenue but the customer name: =INDEX(A2:A9, MATCH(LARGE(C2:C9,1), C2:C9, 0))
For percentile-based thresholds (e.g., 'top 10%'), use PERCENTILE or QUARTILE; LARGE is simpler when you need a fixed rank like 'top 3'.

Related functions

SMALLSMALL returns the k-th smallest value from an array, where k=1 gives the minimum and higher k values give progressively larger results.MAXMAX returns the largest numeric value in a range, ignoring text and empty cells. Useful for finding peak inventory, highest prices, or latest dates.RANK.EQReturns the position of a number in a sorted list, useful for finding how a value ranks among peers in descending (or ascending) order.MEDIANMEDIAN returns the middle value in a sorted list of numbers, or the average of the two middle values if the count is even.INDEXINDEX returns the value or array of values from a specified position within a given range, based on row and optional column numbers.

Frequently asked questions

Does LARGE return only unique values, or does it count duplicates?: LARGE counts duplicates. If your array is {5000, 7200, 5000, 9500}, the sorted descending order is 9500, 7200, 5000, 5000—so LARGE(..., 3) returns 5000 (the first occurrence in rank order), not the next unique value.
How do I find the customer name with the k-th largest revenue?: Wrap LARGE in INDEX/MATCH: =INDEX(A2:A9, MATCH(LARGE(C2:C9, 1), C2:C9, 0)). This finds the k-th largest value, matches it in the revenue column, and returns the corresponding customer name.
Can LARGE work with negative numbers?: Yes. LARGE sorts all numbers—positive, negative, and zero—in descending order. So if your array is {-500, 100, -1000, 50}, LARGE(...,1) returns 100, and LARGE(...,4) returns -1000.
What's the difference between LARGE and RANK?: LARGE returns the actual value (e.g., the $5,000 revenue); RANK returns the position or rank number (e.g., 3rd place). Use LARGE when you need the value, RANK when you need the position.

Need a different formula?

The full generator is not scoped to one function — describe any spreadsheet problem and it will pick.

Open the formula generator

Reviewed 2026-09-17