help search text string based upon list of keywords

[futurejock]

Hi I need a formula that will use a column list of words as "find text," and the formula will tell me if any instance of any keyword in that list occurs anywhere in a single cell text string. Any word only needs to occur, it doesn't matter how many or which or where.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o> </o>
The formula also needs to tell me “no” or indicate even with a blank there was no instance.<o></o>
<o></o>
It needs to be not case sensitive. I also want exact matches only.<o></o>
<o></o>
For instance if:<o></o>
<o></o>
Referencing a list sheet1!A1:E1<o></o>
now<o></o>
dog<o></o>
cow<o></o>
crow<o></o>
corn<o></o>
<o></o>
I have a column of sentences in column B, <o></o>
but want to check each cell individually<o></o>
and sentence in B1 cell is:<o></o>
"The farmer plowed the snow with his dog"<o></o>
<o></o>
I'd like C1 to say: "yes". Because the keyword "dog" is in the cell's text string. (or actually it would be great if it could return a word of my choosing. But y/n, true/false will do.)<o></o>
<o></o>
if the sentence says:<o></o>
"The farmer plowed the snow with his doG"<o></o>
I want the same answer: yes<o></o>
<o> </o>
if the sentence says:<o></o>
"The farmer plowed the snow with his doG and his crow but the cat stayed in the corn for now"<o></o>
I want the same answer: yes<o></o>
<o> </o>
<o></o>
if the sentence says:<o></o>
"The farmer plowed the snow with his John Deere"<o></o>
No keywords were used. I need to make sure the formula doesn't return "yes" on the instance that the keyword "now" (from the list) is part of the word "snow"<o></o>
<o> </o>
Also suppose the keyword list did not include the word "now," but instead said “ow,” I'd need the return to NOT say yes on the instance that the “ow” can be found in “snow” and “plowed.” Also in the absence of “ow” from the list, I’d need “cow” and crow” NOT to return the "ow" combination found in the sentence.<o></o>
<o></o>
It would be a bonus if I could add words to this list and have the formula include automatically the new words, and data automatically update.<o></o>
<o></o>
It would be a bonus I could manually adjust the formula to include additional searches for single keywords from other columnrows of text data. As in if it could do the above, plus add if the word duck appears in text from columnrow Z1, (same row as the cell in question), and even if no key words are in the cell string, also say "yes."<o></o>
<o></o>
Thanks,<o></o>
<o></o>
Dan

 

[Aladin Akyurek]

Sheet1, A1:E1

<TABLE style="WIDTH: 240pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=320><COLGROUP><COL style="WIDTH: 48pt" span=5 width=64><TBODY><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 14.4pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19 width=64>now</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=64>dog</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=64>cow</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=64>crow</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=64>corn</TD></TR></TBODY></TABLE>

Sheet2

<TABLE style="WIDTH: 553pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=738><COLGROUP><COL style="WIDTH: 505pt; mso-width-source: userset; mso-width-alt: 23950" width=674><COL style="WIDTH: 48pt" width=64><TBODY><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 505pt; HEIGHT: 14.4pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" id=td_post_2360531 class=xl65 height=19 width=674>The farmer plowed the snow with his dog</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=64>dog</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19>The farmer plowed the snow with his doG and his crow but the cat stayed in the corn for now</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>corn</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19>The farmer plowed the snow with his John Deere</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 align=middle>#N/A</TD></TR></TBODY></TABLE>

C1, copy down:

=LOOKUP(9.99999999999999E+307,SEARCH(" "&Sheet1!$A$1:$E$1," "&B1),Sheet1!$A$1:$E$1)

 

[swinglow]

first define a named range for your list of key words. It's best if the row or column contains nothing other than your key words. if your key words will be in row one starting in A1, use this formula as the definition of your range:

=OFFSET($A$1,0,0,1,COUNTA($1:$1))

if your list will be in column A starting in A1, use this formula:

=OFFSET($A$1,0,0,COUNTA($A:$A),1)

That formula makes the range dynamic, so if you add words to it, they will be automatically included in the formula.

In my example, I defined gave the range the name "keywords"


case-sensitive:

=IF(SUMPRODUCT(--NOT(ISERROR(FIND(" "&keywords&" "," "&B1&" "))))
>0,"list match","no match")

Not case-sensitive:

=IF(SUMPRODUCT(--NOT(ISERROR(SEARCH(" "&keywords&" "," "&B1&" "))))
>0,"list match","no match")

 

[futurejock]

HI,

Thanks. Thanks for the quick reply, too. I can use this as is and write a separate formula but can you make this formula give:

for returns of #N/A, can it report "NO"
for returns of any keywords, can it report "YES"
?

Dan

 

[futurejock]

Thanks Swinglow.

I can't try your solution right away but first glance it looks nice. The issues case sense, dynamic list, and ability to label return handled.

You have been very helpful.

Dan

 

[swinglow]

no problem. Also, you can use Aladin's formula in conjuction with the dynamic list that I suggested. In Aladin's formula, replace "SEARCH" with "FIND" to make it case-sensitive.

=IF(ISERROR(LOOKUP(9.99999999999999E+307,SEARCH(" "&KeyWords," "&B1),KeyWords)),"NO MATCH","MATCH")

 

[swinglow]

if "cow" is on the list, do you want "the man had a cowface" to be a match, or not a match?

if you want that to be not a match:

=IF(ISERROR(LOOKUP(9.99999999999999E+307,SEARCH(" "&KeyWords&" "," "&B1&" "),KeyWords)),"NO MATCH","MATCH")

or you can still use the formulas I posted earlier

 

[Aladin Akyurek]

HI,

Thanks. Thanks for the quick reply, too. I can use this as is and write a separate formula but can you make this formula give:

for returns of #N/A, can it report "NO"
for returns of any keywords, can it report "YES"
?

Dan

Try to apply an IsNumber test on the Search result...

=IF(ISNUMBER(LOOKUP(9.99999999999999E+307,SEARCH(" "&Sheet1!$A$1:$E$1," "&B1))),"Yes","No")

 

[futurejock]

hi swinglow, hi alladin,

Thanks so much for your help.

SL- I am using 2007 btw, but may need to share with 2003 user so will try the messy.
SL - yes that's a good point, no I would not like cowface, found from cow, or otherwise.

AL- thanks yes I'll try isnumber for y/n (will isnumber disregard case sensitive?)

OK SO HERE'S a NEW thing. Can I have an exclude list within this formula? Can I search for any instance of keywords returns "yes" and a search for any instance of a separate list of keywords switches yes to "no"?

I want all cows but, no jerseys:
"the cow is a jersey" = no
"the cow is a actually a bull"

D

 

[futurejock]

and also would be useful; or even if the previous can't be done:

I would use a formula that requires inclusion of one unique keyword, and then returns yes, but returns no if any instance from a dynamic list occurs.

Dan