INDEX MATCH duplicate value issues

[newbie37]

Hi everyone

I'm having a few issues with duplicate values. In column E below the Sydney value keeps getting repeated. Any tips or better formulas would be much appreciated!

<style><!--table {mso-displayed-decimal-separator:"\."; mso-displayed-thousand-separator:"\,";}@page {margin:.75in .7in .75in .7in; mso-header-margin:.3in; mso-footer-margin:.3in;}td {padding:0px; mso-ignoreadding; color:black; font-size:12.0pt; font-weight:400; font-style:normal; text-decoration:none; font-family:Calibri, sans-serif; mso-font-charset:0; mso-number-format:General; text-align:general; vertical-align:bottom; border:none; mso-background-source:auto; mso-pattern:auto; mso-protection:locked visible; white-space:nowrap; mso-rotate:0;}.xl64 {text-align:left;}.xl65 {mso-number-format:"0\.0%"; text-align:right;}.xl66 {mso-number-format:"0\.0%";}.xl67 {border-top:.5pt solid #BFBFBF ; border-right:none; border-bottom:.5pt solid #BFBFBF ; border-left:none;}.xl68 {mso-number-format:"0\.0%"; border-top:.5pt solid #BFBFBF ; border-right:none; border-bottom:.5pt solid #BFBFBF ; border-left:none;}.xl69 {color:#53ACD6 ; font-weight:700; border-top:none; border-right:none; border-bottom:1.0pt solid #53ACD6 ; border-left:none; white-space:normal;}.xl70 {color:#53ACD6 ; font-weight:700; text-align:right; border-top:none; border-right:none; border-bottom:1.0pt solid #53ACD6 ; border-left:none; white-space:normal;}.xl71 {color:#53ACD6 ; font-weight:700; text-align:left; border-top:none; border-right:none; border-bottom:1.0pt solid #53ACD6 ; border-left:none; white-space:normal;}.xl72 {mso-number-format:0%;}--></style>[TABLE="width: 522"]
<tbody>[TR]
[TD="class: xl69, width: 87"]Row / column[/TD]
[TD="class: xl69, width: 87"]A[/TD]
[TD="class: xl70, width: 87"]B[/TD]
[TD="width: 87"]C[/TD]
[TD="class: xl71, width: 87"]D[/TD]
[TD="class: xl69, width: 87"]E[/TD]
[TD="class: xl70, width: 87"]F[/TD]
[/TR]
[TR]
[TD="class: xl69, width: 87"]1[/TD]
[TD="class: xl69, width: 87"]City[/TD]
[TD="class: xl70, width: 87"]%[/TD]
[TD="width: 87"][/TD]
[TD="class: xl71, width: 87"]Rank[/TD]
[TD="class: xl69, width: 87"]City[/TD]
[TD="class: xl70, width: 87"]%[/TD]
[/TR]
[TR]
[TD="class: xl67"]2[/TD]
[TD="class: xl67"]Sydney[/TD]
[TD="class: xl68, align: right"]5.0%[/TD]
[TD="class: xl72"][/TD]
[TD="class: xl64"]1[/TD]
[TD]Kathmandu[/TD]
[TD="class: xl66, align: right"]4.0%[/TD]
[/TR]
[TR]
[TD="class: xl67"]3[/TD]
[TD="class: xl67"]Riga[/TD]
[TD="class: xl68, align: right"]5.0%[/TD]
[TD][/TD]
[TD="class: xl64"]2[/TD]
[TD]Sydney[/TD]
[TD="class: xl66, align: right"]5.0%[/TD]
[/TR]
[TR]
[TD="class: xl67"]4[/TD]
[TD="class: xl67"]Zurich[/TD]
[TD="class: xl68, align: right"]5.0%[/TD]
[TD][/TD]
[TD="class: xl64"]3[/TD]
[TD]Sydney[/TD]
[TD="class: xl66, align: right"]5.0%[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Kathmandu[/TD]
[TD="class: xl65, align: right"]4.0%[/TD]
[TD][/TD]
[TD="class: xl64"]4[/TD]
[TD]Sydney[/TD]
[TD="class: xl66, align: right"]5.0%[/TD]
[/TR]
[TR]
[TD="class: xl67"]6[/TD]
[TD="class: xl67"]Paris[/TD]
[TD="class: xl68, align: right"]5.0%[/TD]
[TD][/TD]
[TD="class: xl64"]5[/TD]
[TD]Sydney[/TD]
[TD="class: xl66, align: right"]5.0%[/TD]
[/TR]
[TR]
[TD="class: xl67"]7[/TD]
[TD="class: xl67"]Samak[/TD]
[TD="class: xl68, align: right"]7.0%[/TD]
[TD][/TD]
[TD="class: xl64"]6[/TD]
[TD]Samak[/TD]
[TD="class: xl66, align: right"]7.0%[/TD]
[/TR]
</tbody>[/TABLE]


Formula for E2:
=+INDEX($A$2:$A$7,MATCH(SMALL($B$2:$B$7,D2),$B$2:$B$7,0))

Formula for F2:
=+INDEX($B$2:$B$7,MATCH(SMALL($B$2:$B$7,D2),$B$2:$B$7,0))

Thanks everyone!

 

[Aladin Akyurek]

What is the formula that you use if any in column D?

 

[Phuoc]

Try this formula for F2:

=SMALL($B$2:$B$7,ROWS($F$2:F2))

Formula for E2:

=INDEX(A:A,AGGREGATE(15,6,ROW($B$2:$B$7)/($B$2:$B$7=F2),COUNTIF($F$2:F2,F2)))

 

[newbie37]

Many thanks Phuoc!