Excel + Google Sheets · LARGE
Your top 3 in one formula
Returns the 1st, 2nd, 3rd... largest value of a list.
The formula
=LARGE($B$2:$B$6, D2)What each part means
$B$2:$B$6The listD2Which position (1, 2, 3...)
Example
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Rep | Sales | Top | Sales | |
| 2 | Laura | 1,250 | 1 | ||
| 3 | João | 980 | 2 | ||
| 4 | Priya | 2,410 | 3 | ||
| 5 | Tom | 640 | |||
| 6 | Ana | 1,730 |
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Rep | Sales | Top | Sales | |
| 2 | Laura | 1,250 | 1 | 2,410 | |
| 3 | João | 980 | 2 | 1,730 | |
| 4 | Priya | 2,410 | 3 | 1,250 | |
| 5 | Tom | 640 | |||
| 6 | Ana | 1,730 |
Step by step
- Click cell E2.
- Type the formula:
=LARGE($B$2:$B$6, D2) - Press Enter.
- Drag the small square in the bottom-right corner of the cell down to copy it to the other rows.
Where it works
- Every version of Excel
- Google Sheets
- Excel for the web (free)
The same formula in other languages
- English LARGE
- Español K.ESIMO.MAYOR
- Português MAIOR