Match whether a cell value contains a substring in an array

[Murari Rajagopalan]

Hello there,
I am trying to match a specific cell value in column B with the data in Column A and expecting the result in Column C as Yes or No.. In the sense, Column A data should be checked with all values in Column B and if there is a find, it should print as Yes. There will be only one value expected to be matched with Column B as well. Can someone help on this ?
I have attached the excel file as well.

Regards
Murari Rajagopalan

 

[NARAYANK991]

Hi ,

See if this is OK.

Narayan

 

[Murari Rajagopalan]

Hello Narayan,
I just tried by changing the value and this is not working. can you please help to check once ?

 

[bosco_yip]

Maybe,

C2, copied down :

=IF(ISNUMBER(LOOKUP(1,-SEARCH($B$2:$B$10,A2))),"Yes","No")

Regards
Bosco

 

[NARAYANK991]

Hi ,

Sorry , but my proposed formula was wrong. This is more like it ; you can go with Bosco's solution.

=IF(ISNUMBER(LOOKUP(999, SEARCH(B2, $A$2:$A$5))), "Yes", "No")

This assumes that no cell will have more than 999 characters.

Narayan

 

[Murari Rajagopalan]

thank you very much. can i use an arrary in column B instead of one specific value ? I did tried with the formula as attached and this seems to be not working.. In the sense, every column in cell A, will be checked with all values in Cell B rather than a specific value

 

[NARAYANK991]

Hi ,

See if this is OK.

Narayan

 

[Murari Rajagopalan]

Many thanks Narayan. this works.. thanks again for the help. Much appreciated !