List Top 10 Ranked Items (even if 2 items have the same value/rank)

[xtinct2]

Hi --

I have a file where clients are ranked based on revenue figures.
In another table/range I'd like to pull in the top 10 clients.

I've tried doing this by having numbers 1-10 listed in the second table and using index/match to pull the client with the corresponding rank.
However, if two clients have the same revenue amount, then they'd both be ranked w/ a '5', for instance, and the next client in the order would be tagged w/ '7'.
Therefore, using my method, in the seond table only one client is pulled on the '5' line and the '6' line is left blank.

Is there a workaround?

(I'm also realizing that if the highest rank has multiple clients w/ that ranking then it would cause an issue, so maybe the list could pull all of those as well. So if 1-9 are unique, but there are three ranked at '10' then the secondary table shoul pull 12 clients in total)

I hope this is clear enough

Thank you for your help!

[Sam Capricci]

yes, use the rank and count functions together, like this =RANK(A2,$A$2:$A$9,0)+COUNTIF($A$2:A2,A2)-1 then drag down (adjust your ranges to suit).

[xtinct2]

yes, use the rank and count functions together, like this =RANK(A2,$A$2:$A$9,0)+COUNTIF($A$2:A2,A2)-1 then drag down (adjust your ranges to suit).

Hi --

My rank is actually based off of a criteria. Is it possible to convert the formula about to accommodate a criteria (the criteria being location, btw). So if a client has a #1 rank it means they're #1 for that location only, and not overall.
(the other table that pulls will reference a cell with the location i'm looking at, and only those clients will be ranked)

Thank you!

[Sam Capricci]

I went by this statement here.

if two clients have the same revenue amount, then they'd both be ranked w/ a '5'

If you are using text as a criteria I know of no way to rank that.
If I'm misunderstanding you, I suggest you upload a sample workbook with enough representative data AND expected results so we can view what you have and what you want.