Find the cells that contain certain words of another sheet

[MG1992]

Hi,


I have an excel document that contains 2 sheets. I want to look in the first sheet, in column A if the cells contain words from the list in column A of the second sheet. I do not want to find exact cells, but know which cells contain one or more words (in column A of sheet 2) in the texts.

I tried
=IF(ISERROR(SEARCH("*exempleword*";A2));"No";"Yes") - It works great, but now i want to replace ''*exempleword*" by something like "Sheet2!A" but the answer is always No... Can someone help?

 

[gaz_chops]

Try
=IF(ISERROR(SEARCH(Sheet2!A1,A2)),"No","Yes")

 

[MG1992]

I get an error.. Why is there D1?

Try
=IF(ISERROR(SEARCH(D1,A2)),"No","Yes")

 

[gaz_chops]

sorry posted wrong one, try above.

 

[MG1992]

I wanna be able to search on the whole list, that's why i only put Sheet2!A. If i put A1 it's only going to search one cell at a time

 

[gaz_chops]

You said you wanted to "replace ''*exempleword*" by something like "Sheet2!A""
''*exempleword*" is the text you are looking for!

 

[MG1992]

im sorry if it wasnt clear, english isnt my first language, im doind my best... I want to look in the first sheet, in column A if the cells contain words from the list in column A of the second sheet. That's why i'm sayinf that i want something like Sheet2!A, because i want to be able to search at all the cells in columns A (sheet2) to find matches for each cells in sheet 1.

If it helps, in Sheet1 Column A each cells contains a little message. In Sheet2 Column A i have names of people. I want to find which message contains names from the list that i have in sheet2.

I hope i made it clear, im so sorry! Thank you very much

 

[gaz_chops]

Something like
=SUM(IF(ISERROR(SEARCH(Sheet2!A1,A:A,1)),0,1))
You must enter with Ctrl/Shift&Enter

 

[MG1992]

Still not working... i want to find if yes or no cell1 contains a word from the list in sheet2, then same for cell2, etc.

 

[gaz_chops]

OK save confusion, please post an example of the data on both sheets and what results you expect!