Tiebreaker in a Index formula?

[YS1107]

I need a tiebreaker for a formula. Right now the formula reads:
=INDEX(A$3:A$21,MATCH(D28,Y$3:Y$21,0)) which works real well But when there
is a tie it shows on one store twice instead of listing the two separate
stores. I have two stores have the exact scores and it list the store with
the lower store number twice. For example I have store 598 and store 698 both
with a score of 100% but in the ranking of the stores it shows store 598
twice instead of 598 then 698. Is it possible to have a tiebreaker, with the
index formula, that can list the stores in descending order, 598 first and
698 second, or do I need another formula in order for the tiebreaker to work?

Here is what it looks like now:

C28 D28
598 100%
598 100%

I need it to look like this

C28 D28
598 100%
698 100%

If this is not possible I can live without the tiebreaker, I suppose.
Thanks

 

[Luke M]

Next to your stores' scores, use a helper column (I'll assume C) with this
formula:
=RANK(B2,B$2:B$10)

Then, to get the stores in descending order, you can do:
=INDEX(A$2:A$10,MATCH(ROW(A1),C:C,0)

 

[T. Valko]

=RANK(B2,B$2:B$10)

=INDEX(A$2:A$10,MATCH(ROW(A1),C:C,0)

I think you'll get better results with these formulas:

=RANK(B2,B$2:B$10)+COUNTIF(B$2:B2,B2)-1
=INDEX(A$2:A$10,MATCH(ROWS(A$1:A1),C$2:C$10,0)

 

[YS1107]

Thanks Luke and Biff, I got the stores to rank with the formula that Luke
gave me =RANK(B2,B$2:B$10). I put the formula in cells AA3:AA21 which now
reads =RANK(Y3,Y$3:Y$21) and so on, and this ranked the stores based on their
score, which the scores are in column Y3:Y21. The formula that Biff gave
me,=RANK(B2,B$2:B$10)+COUNTIF(B$2:B2,B2)-1,
put the store scores into percentages, is that correct? But the tiebreaker
formula I am still struggling with. The stores are listed in Row A3:A21 and
the store scores are listed in Rows Y3:Y21. But when there is a tie that’s
when I get the problem. I know this is confusing, it is confusing to me too
and I am looking at the spreadsheet, so I will try to show how it is laid out
here:

Cells B28:B32 there is no fomula but I have the numbers 1,2,3,4,5
Cells C28:C32 formula is =INDEX(A$3:A$21,MATCH(D28,Y$3:Y$21,0))
Cells D2832 formula is =LARGE($Y$3:$Y$21,1)
Cells AA3:AA21 formula is =RANK(Y3,Y$3:Y$21)

Cells A3:A21 Cells Y3:Y21 Cells AA3:AA21
Store Score Ranking
123 80% 5
234 87% 4
456 98% 2
567 100% 1
678 92% 3
789 79% 6

Cells C28:C32 Cells D2832
Store Score
567 100%
456 98%
678 92%
234 80%

I have tried to understand how formulas work and I have tried varying the
formula to match the cell numbers I need, with the formulas given to me and I
still don’t get the results I am seeking. I either a circular reference or
the #N/A in the cell. I need a formula that goes into columns C28:C32 that
will fix the tiebreaker issue of listing one store twice. I hope this makes
sense. I really appreciate all the time you guys have spent on this!! I
apologize for my lack of understanding, but I suppose that is why I am here.

 

[YS1107]

Ok I got the formula that Biff gave to work now. I just changed it to
=RANK(Y3,Y$3:Y$21)+COUNTIF(Y$3:Y3,Y3)-1. It did change the numbers to
percentages, why I don't know but I just changed the format from percentages
to general. Now I am still trying to figure out the tiebreaker formula. I
sure it will work but I can't seem to figure out what the parts in this
formula are referring to =INDEX(A$2:A$10,MATCH(ROWS(A$1:A1),C$2:C$10,0). What
is A$2:A$10 referring to? Stores, Scores or the Ranking? What is A$1:A1
referring to? The row with the Stores, scores or ranking? What does C$2:C$10
referring to? The Ranking?

 

[T. Valko]

How are your scores ranked? Highest score is best so its rank is a lower
number or lowest score is best so its rank is a lower number?

This is how a typical ranking order would be:

The rank formula would be: =RANK(B1,B$1:B$5)

Store...Score...Rank
A...99...1
B...95...3
C...95...3
D...92...5
E...99...1

To break the ties and give every score a unique rank the formula would be:

=RANK(B1,B$1:B$5)+COUNTIF(B$1:B1,B1)-1

Store...Score...Rank
A...99...1
B...95...3
C...95...4
D...92...5
E...99...2

Then, to extract the list of stores in order of rank:

Store...Score...Rank
A...99...1
B...95...3
C...95...4
D...92...5
E...99...2

Assume you want the stores listed starting in cell F1. So, enter this
formula in F1:

=INDEX(A$1:A$5,MATCH(SMALL(C$1:C$5,ROWS(F$1:F1)),C$1:C$5,0))

Copy down to F5.

Store...Score...Rank...Store by rank
A...99...1...A
B...95...3...E
C...95...4...B
D...92...5...C
E...99...2...D

 

[YS1107]

It is working! I had to change it to match the correct cells. I just hope by
me changing the formula that I haven't changed the correct results.

Here are the changes I have made:

=RANK(Y3,Y$3:Y$21)+COUNTIF(Y$3:Y3,Y3)-1
=INDEX(A$3:A$21,MATCH(ROWS(A$1:A1),AA$3:AA$21,0)

The only part of the INDEX formula that I don't understand is the (A$1:A1)
part. What is referring to?