How to determine the value?
 

There are lists of text from cell A1 to A10 and from B1 to B20,
For example, under column A
Mary
John
Peter
....
Ann

under column B
John and Mary go to school by bus
Jim eats apple in classroom
....
Ann studies in library

under column C, I would like to know whether the keywords under column A
exists on any statement under column B or not, keyword "Mary" exists on the
first statement, so it returns 1 in cell C1, but no any keyword exist on the
second statement, so it returns 0 in cell C2, keyword "Ann" exists on the
last statement, so it returns 1 in C20.

Does anyone have any suggestions on how to do it in Excel?
Thanks in advance for any suggestions
Eric

How to determine the value?
 

On Wed, 21 Apr 2010 14:36:01 -0700, Eric
wrote:

There are lists of text from cell A1 to A10 and from B1 to B20,
For example, under column A
Mary
John
Peter
...
Ann

under column B
John and Mary go to school by bus
Jim eats apple in classroom
...
Ann studies in library

under column C, I would like to know whether the keywords under column A
exists on any statement under column B or not, keyword "Mary" exists on the
first statement, so it returns 1 in cell C1, but no any keyword exist on the
second statement, so it returns 0 in cell C2, keyword "Ann" exists on the
last statement, so it returns 1 in C20.

Does anyone have any suggestions on how to do it in Excel?
Thanks in advance for any suggestions
Eric

If I understand you correctly, the following **array-entered** formula should
do what you required.

As written, it is case-sensitive. To make it case-INsensitive, change FIND to
SEARCH.

The formula does not check for whole words; so "Annabel is here" will also
match, since Ann is part of Annabel.

This formula must be **array-entered**:

=--(MIN(FIND(IF($A$1:$A$20="",CHAR(1),$A$1:$A$20),B1& CHAR(1)&$A$1:$A$20))<=LEN(B1))

To **array-enter** a formula, after entering
the formula into the cell or formula bar, hold down
<ctrl<shift while hitting <enter. If you did this
correctly, Excel will place braces {...} around the formula.
--ron