Help Using Matching Function

[Gman9]

I have two spreadsheets and on the second sheet I am have a ranking from the first sheet("Season Stats"). I have a ranking of values (column B) (which start with the largest and list the top 5 going down the column ) and I have the name which corresponds to that ranking fill in next to that cell (column A). I am using the following formulas:

Column A-=INDEX('Season Stats'!B2:B18,MATCH(B5,'Season Stats'!D2:D22,0))

Column B-=LARGE('Season Stats'!D2:D22,1)

This works fine except for when Column B returns more than one value of the same number. (Ex. 7,4,4,3,2) If this is the case then Column A lists the same person each time with the same value, even though more than one person has the same value. Is there any way to modify my column A formula to list each person (in consecutive cells down the column) who have that same value? I will give an example of what is showing up:

Column A Column B What I want Column A to show
John 3 John
John 3 Scott
John 3 Joe
Sam 1 Sam
Sam 1 Matt

John, Scott, and Joe all have a value of 3 and Sam and Matt each have a value of 1. How can I distinguish who which value goes with each name? I hope this makes sense. Thanks for your help.

 

[Brian from Maui]

You'll need a ranking system. See,

http://www.mrexcel.com/board2/viewtopic.php?t=49840

 

[Gman9]

The problem with that ranking system in your link is that to get that to work, I have to have a ranking of my values listed on the second sheet. This is something that I can't do. Therefore that example is not going to work. If you look at the example in that link, can i do it without the first couple of columns?

 

[Domenic]

It looks like you're looking for a Top 5 list. Have a look at the following link where Aladin provides an example of an efficient approach...

http://www.mrexcel.com/board2/viewtopic.php?t=69970&highlight=

Note that you can rank your values on your first sheet instead of your second one, if you prefer. Also, it will list any and all ties for 5th place. If, however, you want to avoid helper columns, you can try the following, less efficient approach...

A5, copied down:

=IF(ROWS($A$5:A5)<=COUNTIF('Season Stats'!$D$2:$D$22,">="&LARGE('Season Stats'!$D$2:$D$22,5)),INDEX('Season Stats'!$B$2:$B$22,MATCH(LARGE('Season Stats'!$D$2:$D$22-ROW('Season Stats'!$D$2:$D$22)/10^10,ROWS($A$5:A5)),'Season Stats'!$D$2:$D$22-ROW('Season Stats'!$D$2:$D$22)/10^10,0)),"")

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

B5, copied down:

=IF(ROWS($B$5:B5)<=COUNTIF('Season Stats'!$D$2:$D$22,">="&LARGE('Season Stats'!$D$2:$D$22,5)),LARGE('Season Stats'!$D$2:$D$22,ROWS($B$5:B5)),"")

Note that this too will list all ties for 5th place, if any.

Hope this helps!