Formula for searching names in one go

[SilverChat]

Please help me out ...
here I am attaching snapshots of two excel sheets
1. has description
2. names of persons


What I wanna do is that , I want to check the names of person are matching should indicate me in red or whatever color in different cell from description list. If name is already in the list of "names list" file matching from the list of "names description" file should show me the indication that name is already in the list.
Thanks waiting for snappy response.

 

[Oscar Cronquist]

	A​
1​	one, three, four
2​	six, seven, eight
3​	nine, ten, twelve

Sheet: Sheet1

	A​
1​	one
2​	two
3​	three
4​	four
5​	five
6​	six
7​	seven
8​	eight
9​	nine
10​	ten

Sheet: Sheet2

Conditional Formatting formula applied to cell range A1:A10 (Sheet2) that will change the font color red:
=COUNTIF(Sheet1!$A$1:$A$3,"*"&A1&"*")

 

[SilverChat]

	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]A[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]1[/COLOR]​	one, three, four
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]2[/COLOR]​	six, seven, eight
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]3[/COLOR]​	nine, ten, twelve

<tbody>
</tbody>

Sheet: Sheet1

<tbody>
</tbody>

	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]A[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]1[/COLOR]​	one
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]2[/COLOR]​	two
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]3[/COLOR]​	three
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]4[/COLOR]​	four
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]5[/COLOR]​	five
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]6[/COLOR]​	six
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]7[/COLOR]​	seven
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]8[/COLOR]​	eight
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]9[/COLOR]​	nine
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]10[/COLOR]​	ten

<tbody>
</tbody>

Sheet: Sheet2

<tbody>
</tbody>

Conditional Formatting formula applied to cell range A1:A10 (Sheet2) that will change the font color red:
=COUNTIF(Sheet1!$A$1:$A$3,"*"&A1&"*")

Thanks

 

[Oscar Cronquist]

You are welcome.