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.

CC	CODE		NUMBERS	CODE
237	CMR		18763044	JAM
230	MUS		187635	JAM
61	AUS		23092	MUS
31	NLD		3162	NLD
599	ANT		38050	NOT FOUND
358	FIN		33	FRA
33751	FRA		49185	NOT FOUND
65	SGP		51078	NOT FOUND
1721	SXM		1246	BRB
1876	JAM
81	JPN
124622	BRB

<tbody>
</tbody>

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