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.

 

[Aladin Akyurek]

In E2 control+shift+enter, not just enter, and copy down:

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

 

[Fractalis]

Hello Aladin,

Thanks so much for your help. It works with sample data but when I test with actual data some issues appeared, for example if I have in column A a value with 1 and its CODE is USA, the output for the values that begin with 1 change all to USA and should be JAM and BRB for this sample. (In original sample I replace 81-JPN with 1-USA and highlighted in blue). With this a little bit modified sample the output should be the same as in first post, so the output values in red (USA) are incorrect.

May you help me to fix this. Thanks again.

CC	CODE		NUMBERS	CODE
237	CMR		18763044	USA
230	MUS		187635	USA
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	USA
1876	JAM
1	USA
124622	BRB

<tbody>
</tbody>

 

[Aladin Akyurek]

Sort A12:B13 on CC in ascending order and check this move covers all or most of input variations.

 

[Fractalis]

Sort A12:B13 on CC in ascending order and check this move covers all or most of input variations.

Hello Aladin,

Thanks so much for the help. It seems to work perfectly in the actual data sorting column A as you said. I only have a doubt.

Normally I have stored the numbers as text for other tasks, in this case in order your formula works the numbers should be stored as numbers or as text? I ask this because the formula works perfect when column A is sorted ascending and the sorting gives different result when the values as stored as text or as numbers and because the values have no the same length.

Regards

 

[Aladin Akyurek]

@ Fractalis

The formula does not expect numbers. Sorting in ascending order allows it to evaluate jon12 after jon1, 1001 after 102. So, sort your text numbers under the the sort regime: "Sort anything that looks like a number, as a number".

 

[Fractalis]

@ Fractalis

The formula does not expect numbers. Sorting in ascending order allows it to evaluate jon12 after jon1, 1001 after 102. So, sort your text numbers under the the sort regime: "Sort anything that looks like a number, as a number".

Understood. Very appreciated your help and thanks for share your knowledge. We learn from this kind of solutions.

Regards

 

[Aladin Akyurek]

Understood. Very appreciated your help and thanks for share your knowledge. We learn from this kind of solutions.

Regards

You are welcome. Thanks for providing feedback.

 

[Fractalis]

Hello again Aladin,

Some time ago you helped me with this question and with my data of that moment all requirements were satisfied by your array formula.

Now all is the same, except I'd like to know if you could help me to add one condition to the formula.

I've highlighted in red in column CODE (current output) the values are different for my new condition. In column CODE(Expected) is the output desired.

The condition is that when the IF() evaluates to TRUE or FALSE, if there are more than one matches, then the CODE I like in output would be the first in ascending order. For example, for NUMBER 33 there are 3 matches (334/FRA3, 33750/FRA1, 33751/FRA2). The current output is selecting FRA2 but sorted ascending the 3 matches results that first would be FRA1.

I hope make sense.

Thanks in advance for any help.

COUNTRY CODE	CODE		NUMBERS	CODE (CURRENT)	CODE (EXPECTED)
1	USA		33	FRA2	FRA1
31	NLD		124	BRB92	BRB45
61	AUS		1246	BRB92	BRB7
65	SGP		1721	SXM4	SXM2
124	BRB45		3162	NLD	NLD
230	MUS		23092	MUS	MUS
237	CMR		12462	BRB92	BRB5
334	FRA3		49185	NOT FOUND	NOT FOUND
358	FIN		51078	NOT FOUND	NOT FOUND
599	ANT		187635	JAM	JAM
1246	BRB7		3375012	FRA1	FRA1
1721	SXM2		18763044	JAM	JAM
1721	SXM4
1876	JAM
33750	FRA1
33751	FRA2
124622	BRB5
124623	BRB8
124624	BRB92

<tbody>
</tbody>

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

 

[Aladin Akyurek]

Hello again Aladin,

Some time ago you helped me with this question and with my data of that moment all requirements were satisfied by your array formula.

Now all is the same, except I'd like to know if you could help me to add one condition to the formula.

I've highlighted in red in column CODE (current output) the values are different for my new condition. In column CODE(Expected) is the output desired.

The condition is that when the IF() evaluates to TRUE or FALSE, if there are more than one matches, then the CODE I like in output would be the first in ascending order. For example, for NUMBER 33 there are 3 matches (334/FRA3, 33750/FRA1, 33751/FRA2). The current output is selecting FRA2 but sorted ascending the 3 matches results that first would be FRA1.

I hope make sense.

Thanks in advance for any help.

COUNTRY CODE	CODE		NUMBERS	CODE (CURRENT)	CODE (EXPECTED)
1	USA		33	FRA2	FRA1
31	NLD		124	BRB92	BRB45
61	AUS		1246	BRB92	BRB7
65	SGP		1721	SXM4	SXM2
124	BRB45		3162	NLD	NLD
230	MUS		23092	MUS	MUS
237	CMR		12462	BRB92	BRB5
334	FRA3		49185	NOT FOUND	NOT FOUND
358	FIN		51078	NOT FOUND	NOT FOUND
599	ANT		187635	JAM	JAM
1246	BRB7		3375012	FRA1	FRA1
1721	SXM2		18763044	JAM	JAM
1721	SXM4
1876	JAM
33750	FRA1
33751	FRA2
124622	BRB5
124623	BRB8
124624	BRB92

<tbody>
</tbody>

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

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.