# Top Values In A List

From a list of 30 people with an assigned value, generate a smaller list that are the top n values in that list.

Download the example workbook here: Top n Values in a List.xlsx

## How it all works

Our source data is in A1:D31 and Output begins in H1.

All formulas have error checking to display blanks, also to check that we don’t generate a list longer than the amount we want to show.

For the purpose of this, I won’t include the error handling components to the formulas below however they are in the example sheet.

#### Top n Values

Cell F2 has a drop down validation to choose from 5, 10, 15, 20, 25, 30. This indicates how many values to show.

#### Ranking formula.

We have a Ranking formula included in the source data to make way for duplicate values in Column C. This ensures a unique ranking list, meaning a simple Index + Match formula can be used to look up the Names. Ranking formula starting in C1:

```
=C2+10^-6*ROWS($A2:A$2)
```

#### Getting the Top Value and Top Rank Value

To get the largest to smallest values in the list, use the Large function which only has 2 arguments: array, k (i.e. 1st, 2nd, 3rd largest).

*Top Value:*

```
=LARGE($C$2:$C$31,H2)
```

** Top Rank:**
=LARGE($D$2:$D$31,H2)

#### Getting the First and Last Names based on Value:

Both of these formulas index the Rank column and match the Rank value that has already been calcualted on the same row in column L.

*First Name:*

```
=INDEX($A$2:$A$31,MATCH(L2,$D$2:$D$31,0))
```

*Last Name:*

```
=INDEX($B$2:$B$31,MATCH(L2,$D$2:$D$31,0))
```