I am Trying to Create a Top 5 List @ Bottom of Page

I have a data set.
I would like to be able to select a test from the drop down box at the bottom and have a top 5 or bottom 5 list appear based on high to low or low to high drop down box.

I have played around with rank function, large, small I just can't quite get it to work.
I cleaned up the wordbook so that someone looking at it would have an easier time with it.

thank you in advance.

Try

NCM raw

=INDEX($B$7:$B$36,MATCH(LARGE($F$7:$F$36,ROWS($1:1)),$F$7:$F$36,0))

Score

=LARGE($F$7:$F$36,ROWS($1:1))

Copy both down

Change range to required for other tables


For LOW to HIGH use SMALL

John,

thank you for the forumla,
I am hoping to make it change based on the test I select so NCM Raw, CM Raw ETC also I would like to be able to switch it either accending or decending with the second drop down box.

is this possible?
If not I will do manually but would prefer to have it more customizable.

thank you so much

Slight amendment to John's formula to accommodate your drop-down - put this in B43:

=INDEX($B$7:$B$36,IF($E$42="High to Low",MATCH(LARGE($F$7:$F$36,ROWS($1:1)),$F$7:$F$36,0),MATCH(SMALL($F$7:$F$36,ROWS($1:1)),$F$7:$F$36,0)))

and this in E43:

=IF($E$42="High to Low",LARGE($F$7:$F$36,ROWS($1:1)),SMALL($F$7:$F$36,ROWS($1:1)))

then copy down.

Hope this helps.

Pete

Try this ...

In E43:

=IF($E$42="High to Low",LARGE(OFFSET($D$7:$D$36,,MATCH($B$42,$E$5:$AB$5,0)),ROWS($1:1)),
SMALL(OFFSET($D$7:$D$36,,MATCH($B$42,$E$5:$AB$5,0)),ROWS($1:1)))

In B43 **:

=INDEX($B:$B,SMALL(IF(E43=OFFSET($D$7:$D$36,,MATCH($B$42,$E$5:$AB$5,0)),ROW($B$7:$B$36)),COUNTIF($E$43:E43,E43)))

** Array formula, enter with Ctrl+Shift+Enter.

This worked amazing thank you soo much!!