if cell contains text from list then return value

[AMIT2179]

Need a value 1 if the cells in a particular column contains the below texts.

[TABLE="width: 282"]
<tbody>[TR]
[TD]Chens[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Jin-Xing[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Moh Kok Heng[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Svalinn[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Tan Siow Hoon Terry[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]AIA Singapore[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Please help.

 

[My Aswer Is This]

You need the value 1 entered where if the shown values are found where?

We need specific details to provide you with help.

 

[AMIT2179]

Basically there are many rows and columns of data. I am looking for a formulae in a column which reads contents of one column and if it finds the following words it gives me a "Yes" otherwise a "No".
The words i am looking at are:
[TABLE="class: cms_table, width: 282"]
<tbody>[TR]
[TD]Chens[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Jin-Xing[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Moh Kok Heng[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Svalinn[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Tan Siow Hoon Terry[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]AIA Singapore


[/TD]
[/TR]
</tbody>[/TABLE]

 

[AMIT2179]

Basically there are many rows and columns of data. I am looking for a formulae in a column which reads contents of one column and if it finds the following words it gives me a "Yes" otherwise a "No".
The words i am looking at are:
[TABLE="class: cms_table_cms_table, width: 282"]
<tbody>[TR]
[TD]Chens[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Jin-Xing[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Moh Kok Heng[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Svalinn[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Tan Siow Hoon Terry[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]AIA Singapore

[/TD]
[/TR]
</tbody>[/TABLE]

 

[My Aswer Is This]

This is beyond my knowledgebase.
I will continue to monitor this thread to see what I can learn.

 

[Peter_SSs]

Is this what you mean? If not, more details or examples please.

<b>Check List</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Arial,Arial; font-size:8pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:157px;" /><col style="width:56px;" /><col style="width:18px;" /><col style="width:142px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="font-size:10pt; ">Chens</td><td style="font-size:10pt; ">Yes</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; ">Chens</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="font-size:10pt; ">Other</td><td style="font-size:10pt; ">No</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; ">Jin-Xing</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="font-size:10pt; ">Tan Siow Hoon Terry</td><td style="font-size:10pt; ">Yes</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; ">Moh Kok Heng</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style="font-size:10pt; ">Jin-Xing</td><td style="font-size:10pt; ">Yes</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; ">Svalinn</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td style="font-size:10pt; ">Another</td><td style="font-size:10pt; ">No</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; ">Tan Siow Hoon Terry</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; ">AIA Singapore</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b>Spreadsheet Formulas</b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >B2</td><td >=IF(ISNUMBER<span style=' color:008000; '>(MATCH<span style=' color:#0000ff; '>(A2,D$2:D$7,0)</span>)</span>,"Yes","No")</td></tr></table></td></tr></table>

 

[AMIT2179]

Thanks Peter. So this will work if the formula finds exact match of contents of column A in column D. Can this be modified so that this works when contents of Column A is between the string in Column D? For eg: Chens in A1 is contained in string in D1 so it should return a "Yes".

A	B	C	D
Chens			Chens Ing Pok
Other			Jin-Xing Fung
Tan Siow Hoon Terry			Moh Kok Heng
Jin-Xing			Svalinn
Another			Tan Siow Hoon Terry
			AIA Singapore

<tbody>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]2[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]3[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]4[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]5[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]6[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]7[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]8[/TD]

</tbody>

 

[Peter_SSs]

Can this be modified so that this works when contents of Column A is between the string in Column D? For eg: Chens in A1 is contained in string in D1 so it should return a "Yes".

This is why post 2 asked for specific details.

Try this.
It is an array formula so should be entered without the {} but confirmed with Ctrl+Shift+Enter, not just Enter. If confirmed correctly, Excel will insert the {}. The formula can then be copied down.

<b>Check List</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Arial,Arial; font-size:8pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:149px;" /><col style="width:58px;" /><col style="width:21px;" /><col style="width:132px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="font-size:10pt; ">Chens</td><td style="font-size:10pt; ">Yes</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; ">Chens Ing Pok</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="font-size:10pt; ">Other</td><td style="font-size:10pt; ">No</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; ">Jin-Xing Fung</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="font-size:10pt; ">Tan Siow Hoon Terry</td><td style="font-size:10pt; ">Yes</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; ">Moh Kok Heng</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="font-size:10pt; ">Jin-Xing</td><td style="font-size:10pt; ">Yes</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; ">Svalinn</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style="font-size:10pt; ">Another</td><td style="font-size:10pt; ">No</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; ">Tan Siow Hoon Terry</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; ">AIA Singapore</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b>Spreadsheet Formulas</b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >B1</td><td >{=IF(COUNT<span style=' color:008000; '>(SEARCH<span style=' color:#0000ff; '>(" "&A1&" "," "&D$1:D$6&" ")</span>)</span>,"Yes","No")}</td></tr></table></td></tr><tr><td ><span style=' font-family:Arial,Arial; font-size:9pt; font-weight:bold;'>Formula Array:</span><span style=' font-family:Arial,Arial; font-size:9pt;'><br />Produce enclosing </span><span style=' font-family:Arial,Arial; font-size:9pt; font-weight:bold;'>{ }</span><span style=' font-family:Arial,Arial; font-size:9pt;'> by entering<br />formula with CTRL+SHIFT+ENTER!</span></td></tr></table>

 

[AMIT2179]

Hi Peter, somehow i am not able to see exact formulae but some kind of html code on my screen. Do you see the same? If yes, would you mind typing your solution again? Thanks.