tie scores (stopping/skipping duplicates) using index/match plus small function

First let me start by saying thanks in advance to anyone who can offer a suggestion.

My problem seems simple or so i thought however i am having issues when using the "index/match" function and trying to rank groups using the "small" function. I have added some screen shots of my report below along with the current formulas i have in place for a more informative question for trouble shooting. I have a report that i use to gather data for the surveys my company has to take. It started as a simple sheet where the data is manually input and it calculates the % score for each team, from there i have a cell that averages each team as an office and on the far right side i have a cell that averages the company as a whole. My Sheet 2 is blank. My sheet three i added a table to show the trending % from day to day and the total of all survey types. Below that i have a table (this is where my problem is) i am building to show the bottom 3 employees from each team and their %, these being the lowest 3 performers. My 4th sheet i labeled "data", on this sheet i built a table with an "averageifs" which takes information from sheet 1 and breaks it down to an employee level, basically showing how they rank individually vs a team on sheet 1.

Back to the problem now, on my sheet 3 where i am trying to get the lowest 3 performers to populate, i ran a "small" formula to get my % from the data sheet, placing a 1 and a 2 and a 3 in the three cells for my "k". Seems like all works right, however in the column next to it i have an index (match) to index the employees on the data sheet and have them correspond to the scores on the data sheet and the cell next to them on sheet 3. Again seems like it is working, however right now as the example let's say i have an employee with a 100%, and 2 employees with 50% it will give me employee 1, employee 2 and a duplicate of employee2 vs employee 3.

Essentially i am trying to find a tiebreaker of sorts or not even really that but a way for it to know that when it ranks 1 with a score and it hits the score again to skip the 1 and go to the next. I know using match it is designed to work this way and it's finding the # and assigning it to the first employee on the list each time, but i need it to name each one. Is this possible?

Below are screen shots of my report along with the formulas used.

Sheet 1 (really no formula other than a basic average.
sheet 1.JPG



sheet 3: this is where i am having the problems with it duplicating one employee with both scores even though it's 2(actually in this example it shows the same person with 3 100's when actually it was three different people you will see on the data sheet
sheet 3.JPG

formula being used on sheet 3: on the index it changes as it goes down from F15, F16, to F17 ect. Same on my small, my "k" is 1 then 2 on the next cell, then 3 on the next and so on. these are actually in e15, 16, and 17 for employees and f15, 16 and 17 for %
sheet 3 e15 column 4 employees formula.JPG
sheet 3 f15 column 5 employee % formula.JPG

Data sheet (4) and formula (could only attach 5) (A and B is where i am getting my data for sheet 3) as you see here employees 34584, 35185, and 35221 all have 100's. If you look back on sheet 3 it assigns 34584 to all three vs showing the three different employees.
data sheet and formula.JPG


i thought possibly i need a "countif" added but can't figure out how that would work with the formulas. Again any help is greatly appreciated and let me know if you have any questions.

Thanks
Jay

JPJ,

for tie-breaking adjust the tied values so that every tied value is a little larger/smaller than the other tied values.

Do this using CountIf like so:

Formula:

Please Login or Register  to view this content.

I'm using my own sample sheet to demo this as your data is a little complicated. :-D

See attached.

Cheers

@amit.wilson this option i have tried but in a different way, although i think this could work possibly. My question around it is in "G7" where you have the adjusted score, is there a way to make the numbers still appear normal and just keep everything to the right of the decimal hidden? Also is there a way to add like an "iferror" to this to eliminate the "#value!" because on employee's who don't have any scores yet they will always populate that info and then it will read that as the lowest value. Basically if i have 4 out of 10 people with scores and 6 have no scores it will show my lowest three with "#value!" vs just giving me the lowest 3 with actual scores. I was thinking something like a "=IFERROR,"") but it may not work with your formula. any suggestions on the 2 issues?


***UPDATE: i figured how to get the formula to work and make the "#value!" error go blank, my only problem now is hiding the numbers to the right of the decimal. when i am showing the smallest scores "your report H7:H10" that is where i need this to take place as the "adjusted score" you have in G7:G10" I will be using but will have it hidden on another sheet so it doesn't really matter how it shows.

Thanks
Jay