Index & Match using Data Validation Drop down list

[rrodwell85]

I am creating a Dashboard to display some of the highlights of my data that is being collected. On this Dashboard I want to display the TOP FIVE players based on a their respective scores. But I want to be able to use a Data Validation drop down list to select a certain Project Player Position and then display the TOP FIVE players of that position. All of my data is being stored in a Table on a separate sheet from the Dashboard which is done using a macro.

For example, I will use the macro to enter last name, year, position, etc. and then a score is assigned. Everything up to this point works perfectly. Next, I want to be able to change the Lookup Position ( which is the data validation drop down list) to another position

[TABLE="width: 341"]
<colgroup><col><col><col><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]Lookup Position[/TD]
[TD]Slot Back[/TD]
[/TR]
[TR]
[TD="colspan: 5"]Top 5 Players and Ratings[/TD]
[/TR]
[TR]
[TD]Rank[/TD]
[TD]Last Name[/TD]
[TD]Year[/TD]
[TD]Positon [/TD]
[TD]Score[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Brown[/TD]
[TD]2012[/TD]
[TD]Slot Back[/TD]
[TD]90[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Green[/TD]
[TD]2013[/TD]
[TD]Slot Back[/TD]
[TD]66[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Black [/TD]
[TD]2015[/TD]
[TD]Slot Back[/TD]
[TD]60[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Red[/TD]
[TD]2015[/TD]
[TD]Slot Back[/TD]
[TD]60[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Blue[/TD]
[TD]2015[/TD]
[TD]Slot Back[/TD]
[TD]56
[/TD]
[/TR]
</tbody>[/TABLE]


and then I want the top five players from that position. There may be duplicate scores as shown above.

To find the scores this is the code i have:
=LARGE(Table4[Score],1) *For the first position and so on
I need this code to be dependent on the position

Then, I have this code to find the names:
=INDEX(Table4[Last Name],SMALL(IF(Table4[Score]=$I10,ROW(Table4[Score])),COUNTIF($I$6:$I10,$I$10)))
*Where I6:I10 is the range of the scores

Something isnt working, because when I try to change the Data Validation drop down selection I get a #REF error. CAN SOMEONE HELP ME!

 

[XLearner]

Hi,

I'm under the impression your INDEX(Table4[Last Name],SMALL(IF(Table4[Score]=$I10,ROW(Table4[Score])),COUNTIF($I$6:$I10,$I$10))) formula is directly coded into the Data Validation formula field. If that's the case - and assuming your formula works - I would suggest you drop the formula into a named variable (Ctrl+F3) and call that named variable from the Data Validation field.

Regards
XLearner