Index/match producing #n/a error for duplicate ranks

[Lotus User]

Column A = List of properties
Column B = % participation in a program
Column C = rank based on % high to low (1= highest participation) =SUMPRODUCT(($B$36:$B$46>$B36)/COUNTIF($B$36:$B$46,$B$36:$B$46&""))+1
Column D = sorted rank(index match) with rank listed high to low =INDEX($A$36:$A$46,MATCH($N36,$C$36:$C$46,0))
Column E = index and match the % that goes with the high to low rank =INDEX($B$36:$B$46,MATCH($N36,$C$36:$C$46,0))
Column N = just a list of static numbers 1-11

Problem encountered when the rank # (ie the participation % is the same.) The #N/A is produced on the duplicates. How do I get column D and E to recognize the duplicates and list the 2nd duplicate and not produce an error. If you see from the data below, WPS and WSA are the properties that produce the #N/A error since their % participation is the same as another property and they are listed second.

Thanks!

[TABLE="width: 613"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Column A
[/TD]
[TD] Column B
[/TD]
[TD] Column C
[/TD]
[TD] Column D
[/TD]
[TD] ColumnE
[/TD]
[/TR]
[TR]
[TD]WMB
[/TD]
[TD="align: right"]19.00%
[/TD]
[TD="align: right"]7
[/TD]
[TD] WWJ
[/TD]
[TD="align: right"]62.0%
[/TD]
[/TR]
[TR]
[TD]WPB
[/TD]
[TD="align: right"]21.00%
[/TD]
[TD="align: right"]6
[/TD]
[TD] WSS
[/TD]
[TD="align: right"]42.0%
[/TD]
[/TR]
[TR]
[TD]WSC
[/TD]
[TD="align: right"]41.00%
[/TD]
[TD="align: right"] 3
[/TD]
[TD]WSC
[/TD]
[TD="align: right"]41.0%
[/TD]
[/TR]
[TR]
[TD]WSS
[/TD]
[TD="align: right"]42.00%
[/TD]
[TD="align: right"]2
[/TD]
[TD] WOK
[/TD]
[TD="align: right"]40.0%
[/TD]
[/TR]
[TR]
[TD]WPS
[/TD]
[TD="align: right"]42.00%
[/TD]
[TD="align: right"]2
[/TD]
[TD] WWP
[/TD]
[TD="align: right"]32.0%
[/TD]
[/TR]
[TR]
[TD]WTO
[/TD]
[TD="align: right"]14.00%
[/TD]
[TD="align: right"]8
[/TD]
[TD]WPB
[/TD]
[TD="align: right"]21.0%
[/TD]
[/TR]
[TR]
[TD]WWP
[/TD]
[TD="align: right"]32.00%
[/TD]
[TD="align: right"]5
[/TD]
[TD] WMB
[/TD]
[TD="align: right"]19.0%
[/TD]
[/TR]
[TR]
[TD]WWJ
[/TD]
[TD="align: right"]62.00%
[/TD]
[TD="align: right"]1
[/TD]
[TD]WTO
[/TD]
[TD="align: right"]14.0%
[/TD]
[/TR]
[TR]
[TD]WOK
[/TD]
[TD="align: right"]40.00%
[/TD]
[TD="align: right"]4
[/TD]
[TD] WBP
[/TD]
[TD="align: right"]0.0%
[/TD]
[/TR]
[TR]
[TD]WBP
[/TD]
[TD="align: right"]0.00%
[/TD]
[TD="align: right"]9
[/TD]
[TD]#N/A
[/TD]
[TD="align: center"]#N/A
[/TD]
[/TR]
[TR]
[TD]WSA
[/TD]
[TD="align: right"]0.00%
[/TD]
[TD="align: right"]9
[/TD]
[TD] #N/A
[/TD]
[TD="align: center"]#N/A
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 185"]
<tbody>[TR]
[TD="class: xl64, width: 247, bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]

 

[Aladin Akyurek]

In D36 control+shift+enter, not just enter, copy across to E36, and down:

=INDEX(A$36:A$46,SMALL(IF($C$36:$C$46=SMALL($C$36:$C$46,ROWS(D$36:D36)),ROW($C$36:$C$46)-ROW($C$36)+1),
SUM(IF(SMALL($C$36:$C$46,ROW($C$36:C36)-ROW($C$36)+1)=SMALL($C$36:$C$46,ROWS(D$36:D36)),1))))