Compare range 1 with range 2 and past matched value in range 3

[shafiq2]

Hi,

I am looking for excel function or VBA to compare two different ranges and if any value from range 1 matches with value in range 2 then copy the value from range 2 to range 3, if no value matched then the value in range 3 should be others

As shown in the table below

Sample of data Subject Matched Item Do you like apple Apple sugar is not good for health Sugar Is lemon color yellow? lemon Too much salt is not good salt where is my car other Result Subject Matched Item Do you like apple Apple Lemon sugar is not good for health Sugar Sugar Is lemon color yellow? Lemon Apple Too much salt is not good Salt salt where is my car Other other <colgroup><col style="width: 134pt; mso-width-source: userset; mso-width-alt: 6546;" width="179"> <col style="width: 72pt; mso-width-source: userset; mso-width-alt: 3510;" width="96"> <col style="width: 48pt;" width="64"> <tbody> </tbody>



<tbody>
</tbody><colgroup><col><col><col></colgroup>

 

[Canapone]

Hi

in C2:C5 Lemon-Sugar-Apple-Salt, a possible formula to extract substrings form A2:

=IFERROR(INDEX($C$2:$C$5,MATCH(1,INDEX(SEARCH("* "&$C$2:$C$5&" *",A2&" ",0),0)),"Other")

Hope it helps

 

[shafiq2]

Hi,

Thanks for the formula, I have checked the formula, it brings everything as "other".

Regards,

 

[Canapone]

Hi,

thanks for feedback.

=IF(A2<>"",IFERROR(INDEX($C$2:$C$5;MATCH(1,INDEX(SEARCH("*"&$C$2:$C$5&"*",A2),0),0)),"Other"),"")

A small example here:

http://wikisend.com/download/148246/cercasottostringhe.xlsx Regards

<colgroup><col width="64"></colgroup><tbody>
</tbody>

 

[shafiq2]

Hi,

thanks for feedback.

=IF(A2<>"",IFERROR(INDEX($C$2:$C$5;MATCH(1,INDEX(SEARCH("*"&$C$2:$C$5&"*",A2),0),0)),"Other"),"")

A small example here:

http://wikisend.com/download/148246/cercasottostringhe.xlsx Regards

<tbody>
</tbody>

Thank you for the formula, it works perfectly.

Best Regards,