Formula for Creating a "Top Performer" feature on a production report?

Hello,

I am the marketing manager for my firm and one of my responsibilities include running daily production reports.
These production reports include daily performance metrics of all our sales agents.
I have created a system that weighs each of these metrics and then populates a score accordingly.

Each agent's performance is graded on a routine basis, using their Score as the main factor (in the process of scrubbing this doc, I removed the main metrics that determine the score, so don't pay attention to the stats).
To boost motivation for my agents, I want to implement a simple feature on this report that shows the top 3 performers on a daily basis.
This would appear at the top of the report, as shown below and on the attached file.

There are two things that I need to know to make this work correctly:

1. I'm using the following formulas to pull the top 3 scores in the dataset to the appropriate boxes:
=LARGE(B8:B24,1)
=LARGE(B8:B24,2)
=LARGE(B8:B24,3)

Now I need a formula that can retrieve the agent's name in this same fashion. For example, in the attached doc, the top 3 scores (column B) are 26, 20, and 16. Next to these scores that appear at the top, I need three formulas for cells C2,C3,C4 that would pull over the names that these scores belong to (in this case it would be Shawn R, Michael E, and Brian L.

2. In the event that any of these agents have the same score, resulting in a tie, there would need to be a way to distinguish the scores to the appropriate agents. For example, if Shawn R. and Michael E. are tied with a score of 26, I would need both of their names to appear in the top performer box instead of just one of them twice. The order of their names is not important, just the fact that they both appear in the top 3.

Ideally, I can run these reports every day, sort the scores, and have the top 3 producers update accordingly.

Any ideas?

I really appreciate any help, it would really be useful for me and my agents.

Thanks!

PR.jpg

Put in G2 and copied down :

=IF(ROWS($A$1:A1)<=3,INDEX($A$8:$A$24,MATCH(LARGE(INDEX($B$8:$B$24+(ROWS($B$8:$B$24)-ROW($B$8:$B$24))/10^5,0),ROWS(A$1:A1)),INDEX($B$8:$B$24+(ROWS($B$8:$B$24)-ROW($B$8:$B$24))/10^5,0),0)),"")

This is perfect. Thank you so much azumi!

Another way.

I've borrowed this formula approach from micope21...array-entered in C2 and filled down.

Formula:

Please Login or Register  to view this content.

Array enter means the formula must be committed by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.

Row\Col	C	D	E	F	G
1	Top Performers
2	Brian L			26	C2 :{=INDEX($A$8:$A$24,LARGE(IF($F2=$B$8:$B$24,ROW($A$8:$A$24)-MIN(ROW($A$8:$A$24))+1),COUNTIF($F2:$F$4,$F2)))}
3	Shawn R			26
4	Michael E			16