if cell contains text from list then return value

AMIT2179

New Member
Joined
Nov 2, 2011
Messages
43
Need a value 1 if the cells in a particular column contains the below texts.

Chens
Jin-Xing
Moh Kok Heng
Svalinn
Tan Siow Hoon Terry
AIA Singapore

<tbody>
</tbody>

Please help.
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
You need the value 1 entered where if the shown values are found where?

We need specific details to provide you with help.
 
Upvote 0
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:
Chens
Jin-Xing
Moh Kok Heng
Svalinn
Tan Siow Hoon Terry
AIA Singapore



<tbody>
</tbody>
 
Upvote 0
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:
Chens
Jin-Xing
Moh Kok Heng
Svalinn
Tan Siow Hoon Terry
AIA Singapore


<tbody>
</tbody>
 
Upvote 0
This is beyond my knowledgebase.
I will continue to monitor this thread to see what I can learn.
 
Upvote 0
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>
 
Upvote 0
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
BCD
2ChensChens Ing Pok
3OtherJin-Xing Fung
4Tan Siow Hoon TerryMoh Kok Heng
5Jin-XingSvalinn
6AnotherTan Siow Hoon Terry
7AIA Singapore
8

<tbody>
</tbody>
 
Upvote 0
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>
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,981
Members
448,538
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top