Microsoft Excel - Return the Next of a Duplicate

Anyone ever written a formula that gave you the next available answer for a duplicate value?

I have two columns of data:

TEAM (unique value)
STAT (non-unique value)

I created a ranking list that ranks the STAT column in order from Best to Worst. I used the LARGE formula in order to do this.

Then next to the Ranked Data, I created a new formula that would look for the Ranked Value in the STAT column and respond back with the corresponding TEAM. In this instance I used the INDEX/MATCH formula.

HOWEVER, I have duplicate values and because of that, my INDEX/MATCH formula returns the same value.

An example of this would be:

68.5 Panthers
67.4 Cowboys
65.1 Colts
63.7 Redskins
63.7 Redskins

In this case, instead of returning the Steelers as the next result because the Redskins and Steelers both contained the same value, it returned Redskins because each instance of the formula stops on the first available match. I am trying to get it to return the next TEAM that has the same value.

I will send my spreadsheet to anyone that asks. Mess with it how you want. I've actually been trying to find a formula that does this for the longest time now.

you'll have to rank your non-unique numbers first so that they each get a unique rank.

in a new column enter:

=SUM(1*(a2<$a$2:$a$20))+1+IF(ROW(a2)-ROW($a$2)=0,0,SUM(1*(a2=OFFSET($a$2,0,0,INDEX(ROW(a2)-ROW($a$2)+1,1)-1,1))))

enter using Ctrl+Shift+Enter
then copy down the column

then you can use your rank column to index instead.

hope this helps.