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>

Row / column	A	B	C	D	E	F
1	City	%		Rank	City	%
2	Sydney	5.0%		1	Kathmandu	4.0%
3	Riga	5.0%		2	Sydney	5.0%
4	Zurich	5.0%		3	Sydney	5.0%
5	Kathmandu	4.0%		4	Sydney	5.0%
6	Paris	5.0%		5	Sydney	5.0%
7	Samak	7.0%		6	Samak	7.0%

<tbody>
</tbody>

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!