0

My list has only numbers, with a corresponding name to each score in each row. I am looking for a formula to draw lookup the 1st, 2nd and 3rd names with the corresponding rank. However, in the event of ties, i would like still like to draw the largest number, with preference for names higher on the list.

the list

shrivallabha.redij
  • 5,832
  • 1
  • 12
  • 27
Shaun T
  • 11
  • 1
  • Hi, Welcome to SO .. see [this link](https://www.youtube.com/watch?v=1_v-uqoyXqI) – Naresh May 23 '20 at 06:10
  • 2
    If you don't mind a helper column, then in `C2`: `=RANK.EQ(B2,B$2:B$15,0)+COUNTIF(B$2:B2,B2)-1` drag down. You'll be able to take it from there I'm sure. – JvdV May 23 '20 at 10:54
  • Awesome! Thanks JvdV, it works with the helper column!! i see what you did there. Manual but effective! – Shaun T May 23 '20 at 11:20

1 Answers1

1

In cell E1 insert following formula and then copy down.

=IFERROR(INDEX($A$1:$A$15,SMALL(IF($B$1:$B$15=MAX($B$1:$B$15),ROW($A$1:$A$15),10^7),ROWS($A$1:A1))),"")

Note: This is array formula so it needs to be committed by using CTRL+SHIFT+ENTER simultaneously. If done correctly Excel will wrap formula with braces.

Here's a non-CSE AGGREGATE based formula (based on @Naresh Bhople's suggestion)

=IFERROR(INDEX($A$1:$A$15,AGGREGATE(15,6,IF($B$1:$B$15=MAX($B$1:$B$15),ROW($A$1:$A$15),10^7),ROWS($A$1:A1))),"")

Following is not array formula after removing if condition. Referred this link

=IFERROR(INDEX($A$1:$A$15,AGGREGATE(15,6,(($B$1:$B$15=MAX($B$1:$B$15))/($B$1:$B$15=MAX($B$1:$B$15))*ROW($A$1:$A$15)),ROWS($A$1:A1))),"")

Edit2:

Following is an unwieldy approach but it works with this data and limited testing I did, it is an array formula.

=INDEX($A$2:$A$15,MATCH(SMALL(NPV(-RANK.EQ($B$2:$B$15,$B$2:$B$15)%,$B$2:$B$15)*100+ROW($B$2:$B$15),ROWS($A$1:A1)),NPV(-RANK.EQ($B$2:$B$15,$B$2:$B$15)%,$B$2:$B$15)*100+ROW($B$2:$B$15),0))

shrivallabha.redij
  • 5,832
  • 1
  • 12
  • 27
  • 1
    that's great .. I think we can also use aggregate but not sure how .. i think [this](https://www.youtube.com/watch?v=fDB1Ktyhp3Y) or [this](https://www.youtube.com/watch?v=fDB1Ktyhp3Y&list=RDCMUCJtUOos_MwJa_Ewii-R3cJA&start_radio=1) – Naresh May 23 '20 at 07:01
  • 1
    @NareshBhople in this particular case, OP seems to be interested in Item and not in score so I just used this combination which is more common. We can save CSE by using AGGREGATE. See my edited answer. Thank you. – shrivallabha.redij May 23 '20 at 07:18
  • Noticed that the aggregate formula also has to array formula – Naresh May 23 '20 at 07:30
  • @NareshBhople I have Office 365, what version of office do you have? I don't have to perform CSE on AGGREGATE. – shrivallabha.redij May 23 '20 at 07:31
  • Oh .. Got it .. I have 16 home :) – Naresh May 23 '20 at 07:36
  • 1
    Food for thought: What happens, if say, `B15` is 10? – JvdV May 23 '20 at 09:33
  • That's a good point JvdV made. If the top number is now a distinct value, the table now only returns that top value. It doesn't return the 2nd and 3rd numbers. Is there a way i can change this to include that? – Shaun T May 23 '20 at 09:59
  • @JvdV & ShaunT valid point. Single formula will kind of become tedious, better to stick with helper cells. – shrivallabha.redij May 23 '20 at 13:41