# find ranked value in one column and get value in same row different column

L

#### Legacy 139986

##### Guest
I seem to be finding answers that circle what I'm looking for but not what I want.

In column a I have an advertising source list (TV, Radio, Newspaper, etc.)
In columns b-m I have the number of times that source is sited by month
At the bottom of each month column (b-m) I want to list the top five sources, in rank order, for that month. Using the LARGE function I can get the counts in the right order but I can't figure out how to get the name of the source that corresponds to the value. So, in other words, I don't want the number in the column, I want the label in the corresponding row. It seems like this should be easy but I'm just not getting it.
Thanks for any help.

### Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

#### Scott R

##### Active Member
I assume you need a way to handle "ties" in the rankings. This puts ties in no particular order but at least it doesn't ignore them.

I have desired media type in A2:A7, monthly data in B2:M7, and 1 thru 5 in A9:A13.

Enter this array formula (Ctrl+Shift+Enter) in B9 and copy to B9:M13:

=INDEX(\$A\$2:\$A\$7,MATCH(LARGE(B\$2:B\$7+ROW(B\$2:B\$7)/1000,\$A9),B\$2:B\$7+ROW(B\$2:B\$7)/1000,0))

L

#### Legacy 139986

##### Guest
I assume you need a way to handle "ties" in the rankings. This puts ties in no particular order but at least it doesn't ignore them.

I have desired media type in A2:A7, monthly data in B2:M7, and 1 thru 5 in A9:A13.

Enter this array formula (Ctrl+Shift+Enter) in B9 and copy to B9:M13:

=INDEX(\$A\$2:\$A\$7,MATCH(LARGE(B\$2:B\$7+ROW(B\$2:B\$7)/1000,\$A9),B\$2:B\$7+ROW(B\$2:B\$7)/1000,0))

Wow! Scott, thanks a bunch. I guess it was a little more complicated than I thought it would be. I'm so glad I have resources like you and this forum to get help.

Replies
0
Views
284
Replies
23
Views
2K
Replies
9
Views
2K
Replies
4
Views
421
Replies
1
Views
277

1,190,663
Messages
5,982,158
Members
439,762
Latest member
RoryJ80

### 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.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

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

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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