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))