Sorting a dataset to find the top three values destroys the original order. Worse, it produces a static snapshot that breaks the moment the data changes. The LARGE function solves this elegantly — it returns the Nth highest value from any range without touching the source data. Similarly, the SMALL function returns the Nth lowest value. Together, they power dynamic leaderboards, threshold checks, and conditional summaries that update automatically as data changes.
What Is the Syntax for LARGE and SMALL?
Both functions take exactly two arguments. The structure is identical — only the direction of ranking changes.
| Argument | Required? | What it does |
|---|---|---|
| array | Required | The range or array of numbers to rank. Text, blanks, and logical values are ignored. The range can be a single column, a row, or a multi-column block. |
| k | Required | The rank position to return. Use 1 for the highest (LARGE) or lowest (SMALL) value, 2 for the runner-up, and so on. k must be between 1 and the number of values in the array. |
=IFERROR(LARGE(A2:A100, k), "").
How Do LARGE and SMALL Handle Duplicate Values?
Duplicates are counted separately. For example, if a dataset contains three entries of 90 and 90 is the highest value, then LARGE(array,1), LARGE(array,2), and LARGE(array,3) all return 90. Each duplicate occupies its own rank position.
Consequently, if you want a list of unique top values only, combine LARGE with UNIQUE (Excel 365 only). For older versions, alternatively use a helper column to de-duplicate first.
Example 1: Return the 1st, 2nd, and 3rd Highest Values
The simplest use of LARGE is retrieving specific rank positions from a range. You change only the k argument to step through the rankings. This approach works for any dataset size and updates instantly when the source data changes.
Example 2: Build a Dynamic Top-N Leaderboard
Copying LARGE down a column with k values 1, 2, 3 produces a live leaderboard. Furthermore, locking the array reference with $ signs ensures every formula looks at the same source range. The leaderboard refreshes automatically whenever the underlying data changes.
=LARGE($B$2:$B$100, ROW()-1) where the formula starts in row 2. Each row automatically uses the correct k value. This approach removes the need for a helper column entirely.
Example 3: SUM and AVERAGE the Top N Values
Combining LARGE with SUM or AVERAGE gives you aggregate statistics based on rank rather than the full dataset. For instance, you might need to sum the top 5 sales only. Alternatively, you could average the three best monthly figures, or calculate a bonus from the top quarter of results.
Example 4: Find the Name Associated with the Nth Largest Value
LARGE returns a value, not a name. To find who achieved the top score, combine LARGE with INDEX and MATCH. LARGE retrieves the target value. MATCH then locates that value in the original data column. INDEX subsequently returns the corresponding name from an adjacent column.
Handling Duplicate Values in Name Lookups
When two people share the same score, MATCH always returns the first match. Consequently, the second person's name never appears. For datasets with duplicates, add a tiebreaker column — for instance, combining score with a row number. Alternatively, use FILTER in Excel 365 for a more robust result.
Example 5: Conditional Top-N with LARGE and IF
Standard LARGE searches the entire array. Sometimes, however, you need the top value within a specific category only — for example, the highest sales in the "East" region. Wrapping IF inside LARGE filters the array before ranking.
IF(A:A="East", B:B) passes numbers for East rows and FALSE for all others — LARGE skips the FALSE values and ranks only the genuine East sales.
Example 6: Dynamic Top-N List with FILTER and LARGE
In Excel 365, LARGE combines with FILTER and SEQUENCE to create a self-updating top-N dashboard. The number of rows to display is stored in a single cell. Changing that number instantly rebuilds the entire leaderboard — no formula editing required.
Spilling a Ranked List in One Formula
SEQUENCE generates the k values automatically. LARGE uses those values to retrieve each ranked score. FILTER then extracts the matching rows from the full dataset. All three formulas spill their results into as many rows as needed without any helper columns.
How to Fix Common LARGE and SMALL Errors
#NUM! error
Either k exceeds the number of values in the array, or the array contains no numeric values at all. Check the count of numbers with COUNTA or COUNT. Then confirm k is within that range. Wrapping the formula in IFERROR produces a blank or dash instead of an error for out-of-range k values.
All results are the same value
All values in the dataset are identical, or the array argument is not referencing the correct range. Check the formula's array argument first. Alternatively, the dataset may genuinely contain duplicates — in that case, every rank position returns the same number, which is correct behaviour.
Name lookup returns the wrong person
When combining LARGE with INDEX/MATCH, duplicate scores cause MATCH to return the first matching row every time. As a result, the second person sharing a score is never shown. Resolve this in Excel 365 by switching to FILTER. In older versions, add a tiny unique offset — for instance ROW()/100000 — to create unique values without affecting the displayed data.
Frequently Asked Questions
-
What is the difference between LARGE and MAX?MAX always returns the single highest value. LARGE, by contrast, returns any ranked value — LARGE(array, 1) equals MAX, LARGE(array, 2) gives the second highest, and so on. Use MAX for simplicity when you only need the top value. Use LARGE when you need the 2nd, 3rd, or any Nth highest value, or when building a ranked list.
-
How do LARGE and SMALL handle duplicates?Each duplicate value occupies its own rank position. For example, if three rows each contain 90 and 90 is the highest value, then LARGE(array,1), LARGE(array,2), and LARGE(array,3) all return 90. This matches how competition scoring works. However, when combining LARGE with INDEX/MATCH to find names, duplicates mean MATCH always returns the first matching row — which may not be what you expect.
-
How do I sum the top N values in Excel?Use SUMPRODUCT with LARGE and an array of k values: =SUMPRODUCT(LARGE(B2:B100, {1,2,3,4,5})). This sums the top 5 values. For a variable N stored in a cell, use SEQUENCE: =SUMPRODUCT(LARGE(B2:B100, SEQUENCE(D1))) where D1 holds the number of values to sum. In Excel 365, SUM(LARGE(B2:B100, SEQUENCE(D1))) also works directly.
More Questions About LARGE and SMALL
-
Can I use LARGE with a condition to find the top value in a category?Yes. Wrap IF inside LARGE to filter the array before ranking: =LARGE(IF(A2:A100="East", B2:B100), 1). In older Excel versions, confirm with Ctrl+Shift+Enter instead of Enter. In Excel 365, FILTER provides a cleaner alternative: =LARGE(FILTER(B2:B100, A2:A100="East"), 1). Both approaches return the highest value that meets the condition.
-
What happens if k is larger than the number of values in the array?Excel returns a #NUM! error. For example, LARGE(A2:A10, 11) on a range with only 10 values causes #NUM! because there is no 11th value. To prevent this, wrap the formula in IFERROR: =IFERROR(LARGE(A2:A100, k), ""). Alternatively, validate k against the count of values using COUNT(A2:A100) before passing it to LARGE.
-
Which Excel versions support LARGE and SMALL?Both functions are available in every Excel version from Excel 2000 onwards, including Microsoft 365, Excel 2024, 2021, 2019, 2016, and all earlier versions. They also work in Excel for the web and Google Sheets. The SEQUENCE and FILTER combinations shown in Example 6, however, require Microsoft 365 or Excel 2021.