Vlookup Return the next occurrence

[david2312983]

Hi


I'm attempting to return the next occurrence of a value in a list.


I have a list in column A with 6 %'s in. In column B there is a staff number that corresponds to the person who achieved the %.

[pre]

A       B
100%    1
100%    2
92.31%  3
100%    4
84.62%  5
100%    6

[/pre]
I'm then trying to put these values in a league, in ascending order by the %. The formula I've tried to used is =Large(A1:A6,1),=Large(A1:A6,2), etc. Then for the Staff code to appear next to the % in the league I have used =Vlookup(A1,A1:A6,2,0).


However, as there are multiple entries of 100% the staff code keeps returning 1 for each 100% entry, as it's next to the first 100% entry I presume. So I need to try and find the Nth_Occurrence, but I'm unsure how to do this.


Any help would be appreciated.


Thanks

 

[DaveTurton]

Paste =RANK(A2,A2:A7) into C2 and drag down, that will give you the rank

 

[NARAYANK991]

Hi David ,


If you data is in the range A1:B6 , with the percentages in column A , and the staff IDs in column B , then in any two columns , say columns E and F , enter the following formulae :


E1 : =LARGE($A$1:$A$6,ROW(A1))


F1 : =INDEX($B$1:$B$6,SMALL(IF($A$1:$A$6=E1,ROW($A$1:$A$6)),COUNTIF($E$1:E1,E1)))


The formula in F1 is to be entered as an array formula , using CTRL SHIFT ENTER.


Copy these two formulae downwards.


Narayan

 

[Debraj]

Hi David,


Please try this one also..

=RANK(A1,$A$1:$A$6)+COUNTIF($A$1:A1,A1)-1

Regards,

Deb

 

[david2312983]

Thanks for all your replies. I have solved this now with your support.


Thanks again

 

[Absolution]

Thanks for letting us know David. Out of curiosity, what was your final solution?


Ian