Formula to match the closest value and get corresponding description

[Fractalis]

Hello,

<style type="text/css">
table.tableizer-table {
font-size: 12px;
border: 1px solid #CCC ;
font-family: Arial, Helvetica, sans-serif;
}
.tableizer-table td {
padding: 4px;
margin: 3px;
border: 1px solid #CCC ;
}
.tableizer-table th {
background-color: #104E8B ;
color: #FFF ;
font-weight: bold;
}
</style>May someone help me with a formula if possible for the following table. I see that a normal MATCH function is not working here.

In column A I have the CC values and in column B corresponding description for each value (CODE).

In Column D I have a list of numbers for which I want to look for the closest value in column A and if found put in column E the corresponding description (CODE). The values in red are the output desired for the input NUMBERS in D.


[TABLE="class: tableizer-table"]
<tbody>[TR="class: tableizer-firstrow"]
[TH]CC[/TH]
[TH]CODE[/TH]
[TH][/TH]
[TH]NUMBERS[/TH]
[TH]CODE[/TH]
[/TR]
[TR]
[TD]237[/TD]
[TD]CMR[/TD]
[TD][/TD]
[TD]18763044[/TD]
[TD]JAM[/TD]
[/TR]
[TR]
[TD]230[/TD]
[TD]MUS[/TD]
[TD][/TD]
[TD]187635[/TD]
[TD]JAM[/TD]
[/TR]
[TR]
[TD]61[/TD]
[TD]AUS[/TD]
[TD][/TD]
[TD]23092[/TD]
[TD]MUS[/TD]
[/TR]
[TR]
[TD]31[/TD]
[TD]NLD[/TD]
[TD][/TD]
[TD]3162[/TD]
[TD]NLD[/TD]
[/TR]
[TR]
[TD]599[/TD]
[TD]ANT[/TD]
[TD][/TD]
[TD]38050[/TD]
[TD]NOT FOUND[/TD]
[/TR]
[TR]
[TD]358[/TD]
[TD]FIN[/TD]
[TD][/TD]
[TD]33[/TD]
[TD]FRA[/TD]
[/TR]
[TR]
[TD]33751[/TD]
[TD]FRA[/TD]
[TD][/TD]
[TD]49185[/TD]
[TD]NOT FOUND[/TD]
[/TR]
[TR]
[TD]65[/TD]
[TD]SGP[/TD]
[TD][/TD]
[TD]51078[/TD]
[TD]NOT FOUND[/TD]
[/TR]
[TR]
[TD]1721[/TD]
[TD]SXM[/TD]
[TD][/TD]
[TD]1246[/TD]
[TD]BRB[/TD]
[/TR]
[TR]
[TD]1876[/TD]
[TD]JAM[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]81[/TD]
[TD]JPN[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]124622[/TD]
[TD]BRB[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Thanks in advance for any help.

 

[Fractalis]

Control+shift+enter, not just enter, and copy down:

=IFERROR(IFERROR(INDEX($B$2:$B$20,MATCH("|"&D2&"*",IF(LEN($A$2:$A$20)>LEN($D2),"|"&LEFT($A$2:$A$20,LEN($D2))&"*","|"&$A$2:$A$20&"*"),0)),LOOKUP(9.99999999999999E+307,SEARCH(IF(LEN($A$2:$A$20)>LEN($D2),"|"&LEFT($A$2:$A$20,LEN($D2)),"|"&$A$2:$A$20),"|"&$D2),$B$2:$B$20)),"NOT FOUND")

which appears to fit the desired results you posted.

Hello Aladin,

Thanks for your help again.

Your formula outputs all values correctly, except for number 33.

For NUMBER 33 there are 3 matches (334/FRA3, 33750/FRA1, 33751/FRA2).

In order the formula works, I need to sort Ascending the Country CODEs, that's is ok. But in the output, once these 3 matches were found for number 33, if we sort ascending the CODEs (not the Country Codes) will result in FRA1/33750, FRA2/33751, FRA3/334. Then the CODE Expected would be FRA1 not FRA3

It's possible to instruct the formula this logic?

I hope make sense.

Thanks