Help Using Matching Function

Gman9

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

Excel Facts

Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
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?

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!

Replies
1
Views
448
Replies
0
Views
424
Replies
4
Views
202
Replies
5
Views
192
Replies
5
Views
499

1,221,155
Messages
6,158,248
Members
451,478
Latest member
Nfitzy85

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