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.

 

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

[TABLE="class: cms_table_tableizer-table"]
<tbody>[TR]
[/TR]
[TR="class: cms_table_tableizer-firstrow"]
[TH="align: center"]CC[/TH]
[TH="align: center"]CODE[/TH]
[TH="align: center"][/TH]
[TH="align: center"]NUMBERS[/TH]
[TH="align: center"]CODE[/TH]
[/TR]
[TR]
[TD]237[/TD]
[TD]CMR[/TD]
[TD][/TD]
[TD]18763044[/TD]
[TD]USA[/TD]
[/TR]
[TR]
[TD]230[/TD]
[TD]MUS[/TD]
[TD][/TD]
[TD]187635[/TD]
[TD]USA[/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]USA[/TD]
[/TR]
[TR]
[TD]1876[/TD]
[TD]JAM[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]USA[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]124622[/TD]
[TD]BRB[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

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


[TABLE="class: tg"]
<tbody>[TR]
[TH="class: tg-yyec"]COUNTRY CODE[/TH]
[TH="class: tg-yyec"]CODE[/TH]
[TH="class: tg-5ua9"][/TH]
[TH="class: tg-clhw"]NUMBERS[/TH]
[TH="class: tg-clhw"]CODE (CURRENT)[/TH]
[TH="class: tg-lfzh"]CODE (EXPECTED)[/TH]
[/TR]
[TR]
[TD="class: tg-s268"]1[/TD]
[TD="class: tg-s268"]USA[/TD]
[TD="class: tg-s268"][/TD]
[TD="class: tg-s268"]33[/TD]
[TD="class: tg-eg4j"]FRA2[/TD]
[TD="class: tg-0lax"]FRA1[/TD]
[/TR]
[TR]
[TD="class: tg-s268"]31[/TD]
[TD="class: tg-s268"]NLD[/TD]
[TD="class: tg-s268"][/TD]
[TD="class: tg-s268"]124[/TD]
[TD="class: tg-eg4j"]BRB92[/TD]
[TD="class: tg-0lax"]BRB45[/TD]
[/TR]
[TR]
[TD="class: tg-s268"]61[/TD]
[TD="class: tg-s268"]AUS[/TD]
[TD="class: tg-s268"][/TD]
[TD="class: tg-s268"]1246[/TD]
[TD="class: tg-eg4j"]BRB92[/TD]
[TD="class: tg-0lax"]BRB7[/TD]
[/TR]
[TR]
[TD="class: tg-s268"]65[/TD]
[TD="class: tg-s268"]SGP[/TD]
[TD="class: tg-s268"][/TD]
[TD="class: tg-s268"]1721[/TD]
[TD="class: tg-eg4j"]SXM4[/TD]
[TD="class: tg-0lax"]SXM2[/TD]
[/TR]
[TR]
[TD="class: tg-s268"]124[/TD]
[TD="class: tg-s268"]BRB45[/TD]
[TD="class: tg-s268"][/TD]
[TD="class: tg-s268"]3162[/TD]
[TD="class: tg-s268"]NLD[/TD]
[TD="class: tg-0lax"]NLD[/TD]
[/TR]
[TR]
[TD="class: tg-s268"]230[/TD]
[TD="class: tg-s268"]MUS[/TD]
[TD="class: tg-s268"][/TD]
[TD="class: tg-s268"]23092[/TD]
[TD="class: tg-s268"]MUS[/TD]
[TD="class: tg-0lax"]MUS[/TD]
[/TR]
[TR]
[TD="class: tg-s268"]237[/TD]
[TD="class: tg-s268"]CMR[/TD]
[TD="class: tg-s268"][/TD]
[TD="class: tg-s268"]12462[/TD]
[TD="class: tg-eg4j"]BRB92[/TD]
[TD="class: tg-0lax"]BRB5[/TD]
[/TR]
[TR]
[TD="class: tg-s268"]334[/TD]
[TD="class: tg-s268"]FRA3[/TD]
[TD="class: tg-s268"][/TD]
[TD="class: tg-s268"]49185[/TD]
[TD="class: tg-s268"]NOT FOUND[/TD]
[TD="class: tg-0lax"]NOT FOUND[/TD]
[/TR]
[TR]
[TD="class: tg-s268"]358[/TD]
[TD="class: tg-s268"]FIN[/TD]
[TD="class: tg-s268"][/TD]
[TD="class: tg-s268"]51078[/TD]
[TD="class: tg-s268"]NOT FOUND[/TD]
[TD="class: tg-0lax"]NOT FOUND[/TD]
[/TR]
[TR]
[TD="class: tg-s268"]599[/TD]
[TD="class: tg-s268"]ANT[/TD]
[TD="class: tg-s268"][/TD]
[TD="class: tg-s268"]187635[/TD]
[TD="class: tg-s268"]JAM[/TD]
[TD="class: tg-0lax"]JAM[/TD]
[/TR]
[TR]
[TD="class: tg-s268"]1246[/TD]
[TD="class: tg-s268"]BRB7[/TD]
[TD="class: tg-s268"][/TD]
[TD="class: tg-s268"]3375012[/TD]
[TD="class: tg-s268"]FRA1[/TD]
[TD="class: tg-0lax"]FRA1[/TD]
[/TR]
[TR]
[TD="class: tg-s268"]1721[/TD]
[TD="class: tg-s268"]SXM2[/TD]
[TD="class: tg-s268"][/TD]
[TD="class: tg-s268"]18763044[/TD]
[TD="class: tg-s268"]JAM[/TD]
[TD="class: tg-0lax"]JAM[/TD]
[/TR]
[TR]
[TD="class: tg-s268"]1721[/TD]
[TD="class: tg-s268"]SXM4[/TD]
[TD="class: tg-s268"][/TD]
[TD="class: tg-s268"][/TD]
[TD="class: tg-s268"][/TD]
[TD="class: tg-0lax"][/TD]
[/TR]
[TR]
[TD="class: tg-s268"]1876[/TD]
[TD="class: tg-s268"]JAM[/TD]
[TD="class: tg-s268"][/TD]
[TD="class: tg-s268"][/TD]
[TD="class: tg-s268"][/TD]
[TD="class: tg-0lax"][/TD]
[/TR]
[TR]
[TD="class: tg-s268"]33750[/TD]
[TD="class: tg-s268"]FRA1[/TD]
[TD="class: tg-s268"][/TD]
[TD="class: tg-s268"][/TD]
[TD="class: tg-s268"][/TD]
[TD="class: tg-0lax"][/TD]
[/TR]
[TR]
[TD="class: tg-s268"]33751[/TD]
[TD="class: tg-s268"]FRA2[/TD]
[TD="class: tg-s268"][/TD]
[TD="class: tg-s268"][/TD]
[TD="class: tg-s268"][/TD]
[TD="class: tg-0lax"][/TD]
[/TR]
[TR]
[TD="class: tg-s268"]124622[/TD]
[TD="class: tg-s268"]BRB5[/TD]
[TD="class: tg-s268"][/TD]
[TD="class: tg-s268"][/TD]
[TD="class: tg-s268"][/TD]
[TD="class: tg-0lax"][/TD]
[/TR]
[TR]
[TD="class: tg-s268"]124623[/TD]
[TD="class: tg-s268"]BRB8[/TD]
[TD="class: tg-s268"][/TD]
[TD="class: tg-s268"][/TD]
[TD="class: tg-s268"][/TD]
[TD="class: tg-0lax"][/TD]
[/TR]
[TR]
[TD="class: tg-s268"]124624[/TD]
[TD="class: tg-s268"]BRB92[/TD]
[TD="class: tg-s268"][/TD]
[TD="class: tg-s268"][/TD]
[TD="class: tg-s268"][/TD]
[TD="class: tg-0lax"][/TD]
[/TR]
</tbody>[/TABLE]


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


[TABLE="class: tg"]
<tbody>[TR]
[TH="class: tg-yyec"]COUNTRY CODE[/TH]
[TH="class: tg-yyec"]CODE[/TH]
[TH="class: tg-5ua9"][/TH]
[TH="class: tg-clhw"]NUMBERS[/TH]
[TH="class: tg-clhw"]CODE (CURRENT)[/TH]
[TH="class: tg-lfzh"]CODE (EXPECTED)[/TH]
[/TR]
[TR]
[TD="class: tg-s268"]1[/TD]
[TD="class: tg-s268"]USA[/TD]
[TD="class: tg-s268"][/TD]
[TD="class: tg-s268"]33[/TD]
[TD="class: tg-eg4j"]FRA2[/TD]
[TD="class: tg-0lax"]FRA1[/TD]
[/TR]
[TR]
[TD="class: tg-s268"]31[/TD]
[TD="class: tg-s268"]NLD[/TD]
[TD="class: tg-s268"][/TD]
[TD="class: tg-s268"]124[/TD]
[TD="class: tg-eg4j"]BRB92[/TD]
[TD="class: tg-0lax"]BRB45[/TD]
[/TR]
[TR]
[TD="class: tg-s268"]61[/TD]
[TD="class: tg-s268"]AUS[/TD]
[TD="class: tg-s268"][/TD]
[TD="class: tg-s268"]1246[/TD]
[TD="class: tg-eg4j"]BRB92[/TD]
[TD="class: tg-0lax"]BRB7[/TD]
[/TR]
[TR]
[TD="class: tg-s268"]65[/TD]
[TD="class: tg-s268"]SGP[/TD]
[TD="class: tg-s268"][/TD]
[TD="class: tg-s268"]1721[/TD]
[TD="class: tg-eg4j"]SXM4[/TD]
[TD="class: tg-0lax"]SXM2[/TD]
[/TR]
[TR]
[TD="class: tg-s268"]124[/TD]
[TD="class: tg-s268"]BRB45[/TD]
[TD="class: tg-s268"][/TD]
[TD="class: tg-s268"]3162[/TD]
[TD="class: tg-s268"]NLD[/TD]
[TD="class: tg-0lax"]NLD[/TD]
[/TR]
[TR]
[TD="class: tg-s268"]230[/TD]
[TD="class: tg-s268"]MUS[/TD]
[TD="class: tg-s268"][/TD]
[TD="class: tg-s268"]23092[/TD]
[TD="class: tg-s268"]MUS[/TD]
[TD="class: tg-0lax"]MUS[/TD]
[/TR]
[TR]
[TD="class: tg-s268"]237[/TD]
[TD="class: tg-s268"]CMR[/TD]
[TD="class: tg-s268"][/TD]
[TD="class: tg-s268"]12462[/TD]
[TD="class: tg-eg4j"]BRB92[/TD]
[TD="class: tg-0lax"]BRB5[/TD]
[/TR]
[TR]
[TD="class: tg-s268"]334[/TD]
[TD="class: tg-s268"]FRA3[/TD]
[TD="class: tg-s268"][/TD]
[TD="class: tg-s268"]49185[/TD]
[TD="class: tg-s268"]NOT FOUND[/TD]
[TD="class: tg-0lax"]NOT FOUND[/TD]
[/TR]
[TR]
[TD="class: tg-s268"]358[/TD]
[TD="class: tg-s268"]FIN[/TD]
[TD="class: tg-s268"][/TD]
[TD="class: tg-s268"]51078[/TD]
[TD="class: tg-s268"]NOT FOUND[/TD]
[TD="class: tg-0lax"]NOT FOUND[/TD]
[/TR]
[TR]
[TD="class: tg-s268"]599[/TD]
[TD="class: tg-s268"]ANT[/TD]
[TD="class: tg-s268"][/TD]
[TD="class: tg-s268"]187635[/TD]
[TD="class: tg-s268"]JAM[/TD]
[TD="class: tg-0lax"]JAM[/TD]
[/TR]
[TR]
[TD="class: tg-s268"]1246[/TD]
[TD="class: tg-s268"]BRB7[/TD]
[TD="class: tg-s268"][/TD]
[TD="class: tg-s268"]3375012[/TD]
[TD="class: tg-s268"]FRA1[/TD]
[TD="class: tg-0lax"]FRA1[/TD]
[/TR]
[TR]
[TD="class: tg-s268"]1721[/TD]
[TD="class: tg-s268"]SXM2[/TD]
[TD="class: tg-s268"][/TD]
[TD="class: tg-s268"]18763044[/TD]
[TD="class: tg-s268"]JAM[/TD]
[TD="class: tg-0lax"]JAM[/TD]
[/TR]
[TR]
[TD="class: tg-s268"]1721[/TD]
[TD="class: tg-s268"]SXM4[/TD]
[TD="class: tg-s268"][/TD]
[TD="class: tg-s268"][/TD]
[TD="class: tg-s268"][/TD]
[TD="class: tg-0lax"][/TD]
[/TR]
[TR]
[TD="class: tg-s268"]1876[/TD]
[TD="class: tg-s268"]JAM[/TD]
[TD="class: tg-s268"][/TD]
[TD="class: tg-s268"][/TD]
[TD="class: tg-s268"][/TD]
[TD="class: tg-0lax"][/TD]
[/TR]
[TR]
[TD="class: tg-s268"]33750[/TD]
[TD="class: tg-s268"]FRA1[/TD]
[TD="class: tg-s268"][/TD]
[TD="class: tg-s268"][/TD]
[TD="class: tg-s268"][/TD]
[TD="class: tg-0lax"][/TD]
[/TR]
[TR]
[TD="class: tg-s268"]33751[/TD]
[TD="class: tg-s268"]FRA2[/TD]
[TD="class: tg-s268"][/TD]
[TD="class: tg-s268"][/TD]
[TD="class: tg-s268"][/TD]
[TD="class: tg-0lax"][/TD]
[/TR]
[TR]
[TD="class: tg-s268"]124622[/TD]
[TD="class: tg-s268"]BRB5[/TD]
[TD="class: tg-s268"][/TD]
[TD="class: tg-s268"][/TD]
[TD="class: tg-s268"][/TD]
[TD="class: tg-0lax"][/TD]
[/TR]
[TR]
[TD="class: tg-s268"]124623[/TD]
[TD="class: tg-s268"]BRB8[/TD]
[TD="class: tg-s268"][/TD]
[TD="class: tg-s268"][/TD]
[TD="class: tg-s268"][/TD]
[TD="class: tg-0lax"][/TD]
[/TR]
[TR]
[TD="class: tg-s268"]124624[/TD]
[TD="class: tg-s268"]BRB92[/TD]
[TD="class: tg-s268"][/TD]
[TD="class: tg-s268"][/TD]
[TD="class: tg-s268"][/TD]
[TD="class: tg-0lax"][/TD]
[/TR]
</tbody>[/TABLE]


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