# LARGE function in Excel

> LARGE returns the k-th largest value in a range: k = 1 is the largest, 2 the second largest, and so on.

Source: https://spreadsheetpractice.com/functions/large · Updated October 28, 2026 · Formulas and results on this page were run by the Spreadsheet Practice formula engine on November 20, 2026

## Quick answer

- **What it does:** LARGE returns the k-th largest value in a range: k = 1 is the largest, 2 the second largest, and so on.
- **Syntax:** `=LARGE(array, k)`
- **Most common use:** `=LARGE(B2:B8,1)` returns $51,750 (The largest value, same as MAX)
- **Available in:** Every current version
- **Category:** [Statistical functions](https://spreadsheetpractice.com/functions/category/stats)
- **Returns:** The k-th largest number, or #NUM! if k is less than 1 or more than the number of values.
- **Google Sheets:** Also available in Google Sheets.

## What it does

LARGE returns the k-th largest value in a range: k = 1 is the largest, 2 the second largest, and so on. It's the standard way to build a top 3 or top 10 list, and unlike MAX it can reach past the first place. Duplicate values each take their own position.

## Syntax

`=LARGE(array, k)`

| Argument | Required? | What it is |
|---|---|---|
| `array` | Required | The range or list of numbers to look in. |
| `k` | Required | Which position to return, counting from the largest (1 = largest). |

## Examples

Each result was calculated by running the formula on the sample sheet.

| Cell | Formula | Result | What it shows |
|---|---|---|---|
| F2 | `=LARGE(B2:B8,1)` | $51,750 | The largest value, same as MAX |
| F3 | `=LARGE(B2:B8,2)` | $48,200 | The second-highest sales total |
| F4 | `=SUM(LARGE(B2:B8,{1,2,3}))` | $146,750 | An array of k values sums the top three at once |

### The sample sheet

Every formula above runs on this data:

|  | A | B | C | D | E | F |
|---|---|---|---|---|---|---|
| 1 | Rep | Sales |  |  | Check | Result |
| 2 | Ana | $48,200 |  |  | Top seller | $51,750 |
| 3 | Raj | $39,500 |  |  | Second place | $48,200 |
| 4 | Lee | $51,750 |  |  | Top three combined | $146,750 |
| 5 | Kim | $44,100 |  |  |  |  |
| 6 | Mo | $39,500 |  |  |  |  |
| 7 | Ivy | $28,900 |  |  |  |  |
| 8 | Sam | $46,800 |  |  |  |  |

## Common mistakes

| What you see | Why, and how to fix it |
|---|---|
| #NUM! | k is bigger than the number of values in the range, or less than 1. Check the size of the list. |
| The same number appears twice in a top list | Duplicates each take a position, so two reps tied at $39,500 fill places 5 and 6. That's correct. |
| The wrong range after filling down | Lock the array with $, such as $B$2:$B$8, when k comes from a cell that changes row by row. |

## Tips

- Put 1, 2, 3 in a helper column and use =LARGE($B$2:$B$8,D2) to build a top-N table you can fill down.
- Pair with XLOOKUP to return the name that goes with each top value.

## Related functions

- [SMALL](https://spreadsheetpractice.com/functions/small): SMALL returns the k-th smallest value in a range: k = 1 is the smallest, 2 the second smallest, and so on.
- [MAX](https://spreadsheetpractice.com/functions/max): MAX returns the largest number from a list of values or ranges: the top sale, the latest date, the highest temperature.
- [RANK.EQ](https://spreadsheetpractice.com/functions/rank-eq): RANK.EQ tells you where a number places within a list: 1 for the largest by default, or 1 for the smallest if you set the order to 1.
- [SORT](https://spreadsheetpractice.com/functions/sort): SORT returns a sorted copy of a range, ordered by any column you choose, smallest to largest or the reverse.

## Questions

### How do I find the second highest value in Excel?

Use =LARGE(range,2).

### How do I get the name of the top performer?

Look it up from the value: =XLOOKUP(LARGE(B2:B8,1),B2:B8,A2:A8). Ties return the first match.

## Practice

A graded LARGE exercise ("Top three values") is on [the LARGE page](https://spreadsheetpractice.com/functions/large). It is checked instantly, then re-checked on changed data.

Lessons that use LARGE:

- [Lesson 34: RANK, LARGE & SMALL](https://spreadsheetpractice.com/learn/rank-large-small) (Pro)

---

Spreadsheet Practice (https://spreadsheetpractice.com) teaches spreadsheet formulas by practice: every exercise is graded in a live grid and re-checked on changed data.
