Search for multiple text strings simultaneously in a single cell

geekylawyer

New Member
Joined
May 23, 2013
Messages
3
I have a spreadsheet that has relatively clean data, but at the end of every row is a long notes fields (often filled with several paragraphs of text). I'm trying to search inside each one of those notes to see if it contains certain boilerplate language / legalese / key words.

If I was just looking for one word, it would be easy -- I would write =IFERROR(IF(SEARCH("keyword",E2)>0,"Yes",""),""). That way, if the keyword is present, it returns "Yes", and if it's not present, it returns a blank.

What I want to do, though, if look for a long list of keywords simultaneously, and if ANY of those keywords are present, have it return a "Yes".

So I could do something like =IFERROR(IF(or(SEARCH("keyword1",E2)>0,SEARCH("keyword2",E2)>0,SEARCH("keyword3",E2)>0,SEARCH("keyword4",E2)>0)),"Yes",""),""), but that seems horribly inefficient. Especially since my list of keywords is likely to change over time.

So what I want it to do is search each cell by simultaneous reference to an ever-changing table of keywords (call it [KeywordTable]). And I can't figure out how to do that. The search function is resisting all of my efforts to put multiple search values / a range of words inside of it.

To reiterate: the goal is to look at one cell filled with text, ask "does the text in that cell contain any of the keywords contained in [KeywordTable]", and if the answer is "Yes" return yes, and if the answer is no return no (or blanks). That's all.
 

Some videos you may like

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,371
Hi and welcome to Mr Excel

Maybe...

=IF(SUMPRODUCT(--ISNUMBER(SEARCH(KeywordTable,E2))),"Yes","No")

where KeywordTable is a named range containing all keywords

M.
 

geekylawyer

New Member
Joined
May 23, 2013
Messages
3
That's closer...right now when I click "evaluate formula" though, its giving me this

=if(sumproduct(isnumber({#value!;#value!;26;#value!;Value!}),"Yes",No")
=if(sumproduct(isnumber({false;false;true;false;false}),"Yes","No")
=if(0,"Yes","No")
=No

So, clearly, one of the keywords is present (as evidenced by the "26" and the "true" in the middle of the string). I'm trying to get it to say "if there is even one true, it is all true", but instead, because of the #value! errors for the keywords that aren't present, it's defaulting to a "0"/"false" and a "no". Even though one of the keywords is, in fact, present.
 

geekylawyer

New Member
Joined
May 23, 2013
Messages
3
I got it!! It took a lot of browsing around other forums. But this works:

=IF(COUNT(SEARCH(KeywordTable,E2)),"match","no match")

I just had to hit CTRL+SHIFT+ENTER to turn it into an array function at the end.

Yay!
 

jamtay317

Well-known Member
Joined
Mar 6, 2013
Messages
769
I added a helper column, if you can do that try out this
<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>J</th><th>K</th><th>L</th><th>M</th><th>N</th><th>O</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="font-weight: bold;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #FFFFFF;background-color: #4F81BD;;">data</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="font-weight: bold;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #FFFFFF;background-color: #4F81BD;;">look up</td><td style="font-weight: bold;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #FFFFFF;background-color: #4F81BD;;">Column1</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #DCE6F1;;">the </td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #DCE6F1;;">how</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #DCE6F1;;">yes</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">and </td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">who</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #DCE6F1;;">yes</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">yes</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #DCE6F1;;">they </td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #DCE6F1;;">jan</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #DCE6F1;;">no</td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">them </td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">jane</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #DCE6F1;;">no</td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #DCE6F1;;">is </td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #DCE6F1;;">doe</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #DCE6F1;;">no</td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">and </td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">he</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #DCE6F1;;">no</td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #DCE6F1;;">why</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #DCE6F1;;">him</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #DCE6F1;;">no</td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">how</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">the</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #DCE6F1;;">no</td></tr><tr ><td style="color: #161120;text-align: center;">15</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #DCE6F1;;">who</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #DCE6F1;;">who</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #DCE6F1;;">yes</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">O7</th><td style="text-align:left">=IF(<font color="Blue">ISNUMBER(<font color="Red">MATCH(<font color="Green">N7,L7:L15,0</font>)</font>),"yes","no"</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">O8</th><td style="text-align:left">=IF(<font color="Blue">ISNUMBER(<font color="Red">MATCH(<font color="Green">N8,L8:L16,0</font>)</font>),"yes","no"</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">O9</th><td style="text-align:left">=IF(<font color="Blue">ISNUMBER(<font color="Red">MATCH(<font color="Green">N9,L9:L17,0</font>)</font>),"yes","no"</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">O10</th><td style="text-align:left">=IF(<font color="Blue">ISNUMBER(<font color="Red">MATCH(<font color="Green">N10,L10:L18,0</font>)</font>),"yes","no"</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">O11</th><td style="text-align:left">=IF(<font color="Blue">ISNUMBER(<font color="Red">MATCH(<font color="Green">N11,L11:L19,0</font>)</font>),"yes","no"</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">O12</th><td style="text-align:left">=IF(<font color="Blue">ISNUMBER(<font color="Red">MATCH(<font color="Green">N12,L12:L20,0</font>)</font>),"yes","no"</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">O13</th><td style="text-align:left">=IF(<font color="Blue">ISNUMBER(<font color="Red">MATCH(<font color="Green">N13,L13:L21,0</font>)</font>),"yes","no"</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">O14</th><td style="text-align:left">=IF(<font color="Blue">ISNUMBER(<font color="Red">MATCH(<font color="Green">N14,L14:L22,0</font>)</font>),"yes","no"</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">O15</th><td style="text-align:left">=IF(<font color="Blue">ISNUMBER(<font color="Red">MATCH(<font color="Green">N15,L15:L23,0</font>)</font>),"yes","no"</font>)</td></tr></tbody></table></td></tr></table><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Array Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">J9</th><td style="text-align:left">{=IF(<font color="Blue">O7:O15="yes","yes","No"</font>)}</td></tr></tbody></table><b>Entered with Ctrl+Shift+Enter.</b> If entered correctly, Excel will surround with curly braces {}.
<b>Note: Do not try and enter the {} manually yourself</b></td></tr></table><br />
 

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,371
That's closer...right now when I click "evaluate formula" though, its giving me this

=if(sumproduct(isnumber({#value!;#value!;26;#value!;Value!}),"Yes",No")
=if(sumproduct(isnumber({false;false;true;false;false}),"Yes","No")
=if(0,"Yes","No")
=No

So, clearly, one of the keywords is present (as evidenced by the "26" and the "true" in the middle of the string). I'm trying to get it to say "if there is even one true, it is all true", but instead, because of the #value! errors for the keywords that aren't present, it's defaulting to a "0"/"false" and a "no". Even though one of the keywords is, in fact, present.
You missed the -- before ISNUMBER.
-- coerces (converts) logical values TRUE or FALSE to, respectively, 1 or 0.

M.
 

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,371
I got it!! It took a lot of browsing around other forums. But this works:

=IF(COUNT(SEARCH(KeywordTable,E2)),"match","no match")

I just had to hit CTRL+SHIFT+ENTER to turn it into an array function at the end.

Yay!
Good job! Your formula is correct, but it requires Ctrl+Shift+Enter

M.
 

arooney88

Board Regular
Joined
Feb 17, 2014
Messages
61
For this formula to work for an entire range of cells, you have to first highlight the range you want to formula's to be in, then enter the Array formula, then hit CTRL+SHIFT+ENTER Correct?
 

JohnB2764

New Member
Joined
Nov 9, 2014
Messages
2
Hi and welcome to Mr Excel

Maybe...

=IF(SUMPRODUCT(--ISNUMBER(SEARCH(KeywordTable,E2))),"Yes","No")

where KeywordTable is a named range containing all keywords

M.
Can we build on this? Having checked that the KEYWORD appears in a cell (as above) instead of "YES" it would be useful to have the actual KEYWORD. So IF "YES" THEN relevant keyword.
 

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,371
Can we build on this? Having checked that the KEYWORD appears in a cell (as above) instead of "YES" it would be useful to have the actual KEYWORD. So IF "YES" THEN relevant keyword.
An example


A
B
C
D
1
Text​
Keyword​
Keywords​
2
text key3 text​
Key3​
Key1​
3
key4 text​
Key4​
Key2​
4
text key1​
Key1​
Key3​
5
text text​
Not Found​
Key4​
6
text key2 key4​
Key2​
Key5​

D2:D6 is a named range --> Keywords

Array formula in B2 copied down

=IFERROR(INDEX(Keywords,MATCH(TRUE,ISNUMBER(SEARCH(" "&Keywords&" "," "&A2&" ")),0)),"Not Found")

Confirmed with Crtl+Shift+Enter

Remark: If the cell contains more than one keyword (like A6) the formula returns just the first keyword found.

Hope this helps

M.
 

Watch MrExcel Video

Forum statistics

Threads
1,096,444
Messages
5,450,488
Members
405,613
Latest member
Arpit

This Week's Hot Topics

Top