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>
<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!
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!