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
[TABLE="width: 339"]
<tbody>[TR]
[TD][TABLE="width: 254"]
<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>[TR]
[TD="width: 179, bgcolor: transparent"]Sample of data[/TD]
[TD="width: 96, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Subject [/TD]
[TD="class: xl65, bgcolor: transparent"]Matched[/TD]
[TD="class: xl65, bgcolor: transparent"]Item[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"] Do you like apple



[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"]Apple [/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]sugar is not good for health[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"]Sugar [/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Is lemon color yellow?[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"]lemon [/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Too much salt is not good[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"]salt [/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]where is my car [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"]other[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]Result[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Subject [/TD]
[TD="class: xl65, bgcolor: transparent"]Matched[/TD]
[TD="class: xl65, bgcolor: transparent"]Item[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"] Do you like apple



[/TD]
[TD="class: xl65, bgcolor: transparent"]Apple[/TD]
[TD="class: xl65, bgcolor: transparent"]Lemon[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]sugar is not good for health[/TD]
[TD="class: xl65, bgcolor: transparent"]Sugar [/TD]
[TD="class: xl65, bgcolor: transparent"]Sugar [/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Is lemon color yellow?[/TD]
[TD="class: xl65, bgcolor: transparent"]Lemon[/TD]
[TD="class: xl65, bgcolor: transparent"]Apple [/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Too much salt is not good[/TD]
[TD="class: xl65, bgcolor: transparent"]Salt[/TD]
[TD="class: xl65, bgcolor: transparent"]salt [/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]where is my car [/TD]
[TD="class: xl65, bgcolor: transparent"]Other[/TD]
[TD="class: xl65, bgcolor: transparent"]other[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
</tbody><colgroup><col><col><col></colgroup>[/TABLE]

 

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

<colgroup><col width="64"></colgroup><tbody>
[TD="width: 64"]http://wikisend.com/download/148246/cercasottostringhe.xlsx


Regards
[/TD]

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