MATCH/INDEX showing same name twice

thegg001

New Member
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

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

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

Domenic

MrExcel MVP
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

New Member
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

MrExcel MVP
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

New Member
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

Hope this helps!

Replies
7
Views
683
Replies
1
Views
100
Replies
5
Views
86
Replies
2
Views
341
Replies
2
Views
298

1,196,010
Messages
6,012,840
Members
441,733
Latest member
MartijnB

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back