Hi,
I have a long list of terms, and I need to identify which of these terms appear in another body of text strings. Rather than searching for each individual term, is there a formula that check the terms for me automatically.
The terms will appear in the larger body of text, but they won't appear on their own, so they won't be perfect matches. I've attached a sheet to try to illustrate the problem.
try
=IF(B2="","",IF(ISNUMBER(LOOKUP(10^99,SEARCH(B2,$A$2:$A$12))),"yes","no"))
or even
=IF(COUNTIF($A$2:$A$12,"*"&B2&"*"),"yes","no")
Last edited by martindwilson; 06-30-2014 at 08:55 AM.
"Unless otherwise stated all my comments are directed at OP"
Mojito connoisseur and now happily retired
where does code go ?
look here
how to insert code
how to enter array formula
why use -- in sumproduct
recommended reading
wiki Mojito
how to say no convincingly
most important thing you need
Martin Wilson: SPV
and RSMBC
Using your posted workbook, this regular formula, copied down, returns Yes if any of the list terms exists in the referenced cell...otherwise: No
OOPS! I got the logic reversed.
Please Login or Register to view this content.
THIS regular formula, copied down, returns Yes if the Col_B term exists in any of the Col_A values
Note: I edited that formula to fix a relative reference error.Please Login or Register to view this content.
Is that something you can work with?
Last edited by Ron Coderre; 06-30-2014 at 09:02 AM.
Thanks a lot! I'm not having much luck with this though. I've pasted it wholesale into column C. The references seem to be correct, but I just get the error dialogue box.
=IF(B2<>"";IF(COUNTIF($A$2:$A$12;"*"&B2&"*");"Yes","No");"")
try using ; instead of ,
or maybe
=WENN(B2<>"";WENN(ZÄHLENWENN($A$2:$A$12;"*"&B2&"*");"Yes","No");"")
Last edited by martindwilson; 06-30-2014 at 09:11 AM.
Thanks all for you help. Unfortunately, I was unfamiliar with the methods which you explained, so I couldn't adapt them. I think I did find a solution though:
=VLOOKUP("*"&B2&"*";A2:C12;3;FALSE)
I added in a full column of "YES" in column C. So now I get "YES" if the term appears, if not I get N/A which suits me entirely. (To be honest, I didn't know how to use wildcards, until I saw the formulas that you provided...)
Thanks again!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks