I am stumped to find the right formula to accurately return the top three ranked outcomes from a range in all scenarios. When the rankings are not equal things are always fine, 1,2,3,4,5. However when the top 2 or 3 outcomes are tied for first place, say 1,1,1,4,5, I cannot find the right formula to return all top 3 accurately.
Ranking of Locations
The array 'Helper' formula range at E3:E6 works perfectly with 'Top 3' range at F3:F6 so the rank sorting logic is working fine.
Helper Arrays
Top 3 reference Range
The final step of creating a 'Top 3 List' range at G3:G6 is where it goes wrong. I have tried many variants, including array formulae which I am new to, but none work. The best so far is a MATCH & SMALL combo but not good with duplicates. (see below)
Top 3 - List' formula not grabbing all 3 #1 locations
Please if someone knows a formula which will return an accurate 'Top 3 list' even when there are duplicate rankings? Thanks!
PS: This is my first request post. Stackoverflow has helped me MANY times
No duplicates -Everything is fine
One duplicate score - 1st and 2nd top are actually #1 twice
Two duplicate scores - 1st, 2nd and 3rd top are actually #1 3x
This answer will return the top three results in the order they appear in your list. It may even be possible to eliminate the helper column but I did not test that far. I reordered your list so that it was not simply grabbing the first three rows. Otherwise it would be as simple as sorting your list and linking to the top three lines.
Helper column to grab the top three scores
It assumes your data is in B4:C8. Copy the formula down until you have it in three rows. Aggregate with 14 will sort you list from largest to smallest. Row(A1) will return the first value in the sorted list. when its copied down it will increase to row(A2) so it will return the second value in the sorted list and so on.
Matching the sorted score to the names
This assumes your top three list of scores is in E4:E6
Its finding all the rows numbers that match the number in your sorted top three list. When there is a repeat, it advances to the next row number in the matching list due to the countif. The row number is then passed to INDEX which in turn pulls the name.