I have a formula that searches a list of percentages per employee and shows me what percentage and what employee are in the bottom 5. One flaw that I have been unable to figure out is how to skip to the next person that has the same value.
This is how it looks now:
How I'd like it to look:
The formula that I am using currently for score is:
=SMALL(Table1[Score],I28)
and for the agent I am using a match formula:
=INDEX(Table1[Agent],MATCH(K28,Table1[Score],0))
Any ideas? Something tells me I am missing something within the match function.
This is how it looks now:
Rank | Agent | Score |
1 | Bob | -100% |
2 | Bob | -100% |
3 | Rick | -60% |
4 | Mary | -20% |
5 | Sue | 0% |
How I'd like it to look:
Rank | Agent | Score |
1 | Bob | -100% |
2 | Rick | -60% |
3 | Mary | -20% |
4 | Sue | 0% |
5 | Steve | 0% |
The formula that I am using currently for score is:
=SMALL(Table1[Score],I28)
and for the agent I am using a match formula:
=INDEX(Table1[Agent],MATCH(K28,Table1[Score],0))
Any ideas? Something tells me I am missing something within the match function.