Extract values considering dublicates

[waldymar]

I have a table like below:

<TABLE style="WIDTH: 300pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=400><COLGROUP><COL style="WIDTH: 160pt; mso-width-source: userset; mso-width-alt: 7789" width=213><COL style="WIDTH: 140pt; mso-width-source: userset; mso-width-alt: 6838" width=187><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 160pt; HEIGHT: 15pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" height=20 width=213>[FONT=Calibri]CA PREF FUND TRUST [/FONT][FONT=Arial][SIZE=2][B]2049 7%[/B][/SIZE][/FONT]</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 140pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" width=187 align=right>[FONT=Calibri]18[/FONT]</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" height=20>[FONT=Calibri]CLAUDIUS CS [/FONT][B][SIZE=2][FONT=Arial] 2049 7.875%[/FONT][/SIZE][/B]</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" align=right>[FONT=Calibri]153[/FONT]</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" height=20>[FONT=Calibri]DUBAI GOVT [/FONT][FONT=Arial][SIZE=2][B]2015 6.7%[/B][/SIZE][/FONT]</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" align=right>[FONT=Calibri]84[/FONT]</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" height=20>[FONT=Calibri]DUBAI GOVT [/FONT][FONT=Arial][SIZE=2][B]2020 7.75%[/B][/SIZE][/FONT]</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" align=right>[FONT=Calibri]84[/FONT]</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" height=20>[FONT=Calibri]DUBAI ELEC&WATER 2020 [B]7.375%[/B][/FONT]</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" align=right>[FONT=Calibri]101[/FONT]</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" height=20>[FONT=Calibri]Bahrain GOVT 2017 [B]5%[/B][/FONT]</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" align=right>[FONT=Calibri]168[/FONT]</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" height=20>[FONT=Calibri]IPIC GMTN 2020 [B]5%[/B][/FONT]</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" align=right>[FONT=Calibri]126[/FONT]</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" height=20>[FONT=Calibri]ATLANTIC ALDAR [B]8.75%[/B][/FONT]</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" align=right>[FONT=Calibri]138[/FONT]</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" height=20>[FONT=Calibri]LLOYDS TSB [B]6.5%[/B][/FONT]</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" align=right>[FONT=Calibri]64[/FONT]</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" height=20>[FONT=Calibri]HSC Shares [B]4.61%[/B][/FONT]</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" align=right>[FONT=Calibri]168[/FONT]</TD></TR></TBODY></TABLE>

I wish to create another table which will sort the data by numbers in ascending order and extract the corresponding values. For example, I need that number 18 is the first choice and it gives me CA PREF FUND TRUST 2049 7%, then 64 with corresponding value, then I have two records with 84. Here is the problem. I'm using formula:

=index($B$1:$B$9,match(small($C$1:$C$9,F1),$C$1:$C$9,0))

It's ok, but in case of records 84 it gives me the same values for both. How to make it that it gives me the 2nd value for 84 record?
Thanks in advance

 

[Marcelo Branco]

Hi,

Maybe this

Array-formula in D1
=INDEX($B$1:$B$10,MATCH(SMALL($C$1:$C$10+ROW($C$1:$C$10)*10^-6,ROWS($1:1)),$C$1:$C$10+ROW($C$1:$C$10)*10^-6,0))

confirmed with Ctrl+Shift+Enter
(hold down both Ctrl and Shift keys and hit Enter)

copy down

HTH

M.

 

[waldymar]

Hi,

Maybe this

Array-formula in D1
=INDEX($B$1:$B$10,MATCH(SMALL($C$1:$C$10+ROW($C$1:$C$10)*10^-6,ROWS($1:1)),$C$1:$C$10+ROW($C$1:$C$10)*10^-6,0))

confirmed with Ctrl+Shift+Enter
(hold down both Ctrl and Shift keys and hit Enter)

copy down

HTH

M.

Thanks a lot, it works! Just one point, my true range is not only 10 cells but 100, many of which are blank. How this formula can disregards the blank cells?

 

[waldymar]

Please I need your assistance. How this formula can disregard the blank values and start with the smallest non-zero record?

 

[waldymar]

I really need your help!

 

[Ingolf]

Hi,
If you use Excel 2007 or 2010
Try this:

=IFERROR(INDEX($B$1:$B$10,MATCH(SMALL($C$1:$C$10+ROW($C$1:$C$10)*10^-6,ROWS($1:1)),$C$1:$C$10+ROW($C$1:$C$10)*10^-6,0)),"")

confirmed with Ctrl+Shift+Enter

 

[waldymar]

Hi,
If you use Excel 2007 or 2010
Try this:

=IFERROR(INDEX($B$1:$B$10,MATCH(SMALL($C$1:$C$10+ROW($C$1:$C$10)*10^-6,ROWS($1:1)),$C$1:$C$10+ROW($C$1:$C$10)*10^-6,0)),"")

confirmed with Ctrl+Shift+Enter

IFERROR doesn't work in this case!
Someone who know how to disregards blank cells in my formula, please help me!

 

[Marcelo Branco]

Thanks a lot, it works! Just one point, my true range is not only 10 cells but 100, many of which are blank. How this formula can disregards the blank cells?

To disregard the blank rows, maybe this array-formula

=IFERROR(INDEX($B$1:$B$100,MATCH(SMALL(IF($C$1:$C$100<>"",$C$1:$C$100+ROW(C$1:$C$100)*10^-6),ROWS($1:1)),$C$1:$C$100+ROW($C$1:$C$100)*10^-6,0)),"")

Ctrl+Shift+Enter

M.