MATCH/INDEX showing same name twice

[thegg001]

Hi,

I am doing a spreadsheet and i need to cross reference the highest number in one column and add the name of that row in another column for example:

=INDEX($A$4:$A$100,MATCH(E102,$J$4:$J$100,0))

E102 is the cell where the highest number is put, it has this formula:

=LARGE($J$4:$J$100,1)

i need to do this for the top 5 highest numbers but when two rows have the same number it gives the same name twice like this:

Winners Points
offelia 17
offelia 17
louis 13
raymond 10
nadine 9

Can anyone show me how to correct this? I have a feeling its gonna be an IF function but cant figure it out.

 

[Domenic]

Here's a formula system that will take into consideration any ties for 5th place...

Assumptions:

A4:A100 contains the name

J4:J100 contains the points

Formulas:

K4, copied down:

=RANK(J4,$J$4:$J$10)+COUNTIF($J$4:J4,J4)-1

L3: enter 5, indicating that you want a Top 5 list

M3:

=MAX(IF(J4:J100=INDEX(J4:J100,MATCH(L3,K4:K100,0)),K4:K100))-L3

...confirmed with CONTROL+SHIFT+ENTER, not just ENTER

N4, copied down:

=IF(ROWS(N$4:N4)<=$L$3+$M$3,INDEX(A$4:A$100,MATCH(ROWS(N$4:N4),$K$4:$K$100,0)),"")

O4, copied down:

=IF(ROWS(O$4:O4)<=$L$3+$M$3,INDEX(J$4:J$100,MATCH(ROWS(O$4:O4),$K$4:$K$100,0)),"")

Hope this helps!

 

[thegg001]

it does a little, but i was kinda vague on the description so im gonna try and be more indepth. i have 80 some rows with individual cashier names, then 6 columns each with certain data. on the last column i have the total points for each cashier, its basically a contest. so down on cell E102-106 i have the formulas:
=LARGE($J$4:$J$100,1)
=LARGE($J$4:$J$100,2)
=LARGE($J$4:$J$100,3)
=LARGE($J$4:$J$100,4)
=LARGE($J$4:$J$100,5)

then on cells C102-106 i have the match/index:
=INDEX($A$4:$A$100,MATCH(E102,$J$4:$J$100,0))
=INDEX($A$4:$A$100,MATCH(E103,$J$4:$J$100,0))
=INDEX($A$4:$A$100,MATCH(E104,$J$4:$J$100,0))
=INDEX($A$4:$A$100,MATCH(E105,$J$4:$J$100,0))
=INDEX($A$4:$A$100,MATCH(E106,$J$4:$J$100,0))
but if 2 cashiers have the same point totals it will show the same person twice instead of showing them both, which is what i need

 

[Domenic]

The reason I offered you that solution is that if there are two or more people tied for 5th place, each one would be listed. Whereas your approach will list only the top five without any regard for ties. If for some reason you don't care about ties for 5th place, then you could try the following...

C102, copied down:

=INDEX($A$4:$A$100,MATCH(LARGE($J$4:$J$100-ROW($J$4:$J$100)/10^10,ROWS($C$102:C102)),$J$4:$J$100-ROW($J$4:$J$100)/10^10,0))

E102, copied down:

=INDEX($J$4:$J$100,MATCH(LARGE($J$4:$J$100-ROW($J$4:$J$100)/10^10,ROWS($E$102:E102)),$J$4:$J$100-ROW($J$4:$J$100)/10^10,0))

Both these formulas need to be confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

Hope this helps!

 

[thegg001]

I tried your fisrt suggestion again and its along the same lines as what i need but instead of rows can i have the top 5 cashiers along the bottom of the columns in a 2X5 box of sorts with names on one side and points on the other, i do need to seperate any ties among points. Id be happy to email anyone my spreadhseet so they could get a clearer picture.

Thanks again for all your time and patience

 

[Domenic]

I've received your email, made the necessary adjustments, and emailed you the file.

Hope this helps!