Different perspective of RANK

[swansonray]

I am trying to solve a problem I am having with RANK. I found some great
information that is posted on “cpearson.com†and I see what it does but the
formulas have to be in the same rows that the data is in. What I am looking
for is to populate the 1st place for a caption in a separate row.
“=OFFSET(B$11,MATCH(SMALL(K$11:K$13,ROW()-ROW(K$11)+1),K$11:K$13,0)-1,0)â€
works if data is in same row.
Info:
Column B ColumnK
Name Rank
Group1 2
Group2 1
Group3 3

Now in a row below the above data and in a separate column I want to display
the name that ranked 1st. ie Group2.

And finally if there is a tie for first in the data I want to display
“Group2 and Group(Tied Group)†in tha same cell.

 

[Pete_UK]

You could put this in, say, L5 if your data is in column B and column
K as in your example:

=INDEX(B:B,MATCH(1,K:K,0))

It will return the name from column B which has a rank of 1 in column
K.

Hope this helps.

Pete

 

[swansonray]

This does produce the said result and works if there is only 1 group ranked
1st. Thank you. But what about the second scenario if there are two groups
ranked 1 (tied) I would like to display "Group1 and Group2" or "Group1 &
Group2"

 

[Pete_UK]

Do you only have 3 groups, as in your example (and formula), or could
there be a lot more in practise?

Pete

 

[Pete_UK]

I'm away for a few days from tomorrow, so if you do reply then your
response is likely to be lost when I get back. Assuming that you do
only have 3 groups, then you could do this:

=IF(K11=1,B11&IF(OR(K12=1,K13=1)," and ",""),"")&IF(K12=1,B12&IF
(K13=1," and ",""),"")&IF(K13=1,B13,"")

This caters for all three groups being tied, for 2 out of 3 being
tied, as well as for only one group having a rank of 1.

If you have more than three groups then the above approach would be a
bit unwieldy.

Hope this helps.

Pete