# Ranks and Index and Match

#### Nevsy

##### New Member
Good Morning everyone

i have a data set that takes stock holdings from a portfolio and lists it alphabetically and the holding percentage (that is how the data comes in). in a column i have used the rank function to determine what rank within the portfolio each holding is. now, as bad luck would have it, the percentage holdings come to 2 decimal places and therefore it often happens that two holdings are the same size and so the rank function returns the same rank.

in another part of the spread sheet i have a list showing the top 10 holdings and to do this i use the index and match function to pull hold the holdings ranked 1 to 10.

however, lets say there are 2 holdings both ranked 9: therefore, there is no holding ranked 10 so i get an error and i am unable to design a formula to find the second holding that is ranked at 9.

any ideas?

thanks

Nevsy

### Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
Hi

Assume the percentage is in col B and your rank is in col C (cells B2:B10 for the sake of argument). In col C use this formula to rank:

=RANK(B2,\$B\$2:\$B\$10)+COUNTIF(\$B\$2:\$B2,\$B2)-1

This will give you a list without duplicates based on the percentage and then based on the actual position within the list for duplicates (ie those appearing first rank higher).

that was bang on the money, thank you for that Richard

oh, and weldone on gaining MVP status last October

You're welcome

Thank you!

Replies
0
Views
330
Replies
1
Views
698
Replies
3
Views
236
Replies
1
Views
172
Replies
2
Views
362

1,203,028
Messages
6,053,123
Members
444,640
Latest member
Dramonzo

### 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.

### Which adblocker are you using?

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