Formula to Return Bottom 3 Values From a Table Based on a Criteria

I don't want to use a Pivot Table to do this, i really need a formula!

I have a list of people, where they are based (region) and a score for their performance.

I can use SMALL to return the bottom 3 scores no problem, and then use index/match to give me the name of the person who achieved that score.

But i then have an issue returning the corresponding person if more than 1 person has achieved the same score.

On top of this, i would like the user to be able to select which region to see the bottom 3 performers for.

Any help would be greatly appreciated! As i'm stuck!

Thanks in advance

J

F18= =index($a$18:$a$27,match(small($c$18:$c$27,e18),c18:c27))

This is better solution
=INDEX(IF($C$18:$C$27=F18,$A$18:$A$27),E18)

Thanks very much, that seems to solve the issue of two people achieving the same score,

but any help on allowing the user to select which region to see that bottom 3 engineers for?

Good spot Crzu, obviously my real data is much larger and the scores are randomly spread!

Originally Posted by JungleJme

Good spot Crzu, obviously my real data is much larger and the scores are randomly spread!

I am sorry,
I am trying to correct it

Originally Posted by eisayew

I am sorry,
I am trying to correct it

No problems, i thank you for your help! I'm still banging my head against a brick wall in the background so all/any help is really appreciated!

OK, so i've figured out how to filter the result based on region.

I've added a helper column that populates the score depending on which region is selected. Then you can simply do the SMALL calculation on the helper column to return the 3 smallest values.

However, i still have the same problem that if two engineers get the same score, it will only return the name of the 1st Engineer

Sorry Fotis, missed it! Must have been posting myself!

I also came up with a result using a helper column on the data to show the value if the region was selected, but yours is tidier!

thanks a lot to all of you

I know its a bit late
I was trying to solve this formula without helper column. But with a helper column I have this formula
=INDEX($A$18:$A$27,MATCH(1,($C$18:$C$27=F18)*(COUNTIF($F$18:F18,F18)=$D$18:$D$27),0)) CSE

Thanks Eisayew! I'll try yours out as well, just out of professional curiosity