# Why does LARGE return #NUM! for a top 5 list?

You asked for more places than there are numbers. Leave the extra places blank: =IF(D2>COUNT($B$2:$B$6),"",LARGE($B$2:$B$6,D2)).

**Excel:** `=IF(D2>COUNT($B$2:$B$6),"",LARGE($B$2:$B$6,D2))` (Every current version)

**Google Sheets:** the same formula works.

## Example

Sample data (row 1 is the header):

| A | B | C | D | E |
| --- | --- | --- | --- | --- |
| Player | Score |  | Rank | Score |
| Ana | 8420 |  | 1 |  |
| Ben | 9115 |  | 2 |  |
| Cara | 7760 |  | 3 |  |
| Dev | 8890 |  | 4 |  |
|  |  |  | 5 |  |

Formula in E2, filled to E6: `=IF(D2>COUNT($B$2:$B$6),"",LARGE($B$2:$B$6,D2))`

Result: 9115; 8890; 8420; 7760; 

## How it works

LARGE(range, k) returns the k-th largest number, and k can't be larger than the count of numbers. With four scores there is no 5th place yet, so LARGE returns #NUM!. COUNT says how many scores there are, and IF leaves the extra places blank until more scores arrive.

## Other ways

- `=IFERROR(LARGE($B$2:$B$6,D2),"")`: Shorter, but it would hide any other error too.

## Common mistakes

- A k of 0 or less also returns #NUM!.
- Scores stored as text aren't counted, which makes k "too large" sooner than you expect.

## Questions

**Does SMALL work the same way?**

Yes, and it returns #NUM! for the same reason.

**How do I list the top 5 in Excel 365?**

`=TAKE(SORT(B2:B6,,-1),5)` returns as many as exist, without errors.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/large-returns-num-error