Combine "Does Not Contain" with A Sumproduct (Isnumber(Search Formula

Try adding spaces around the search words, i.e.:

=SUMPRODUCT(--ISNUMBER(SEARCH(" "&Retailers_Tracked&" ",A2,1)/LEN(Retailers_Tracked)))>0

That works if my keyword is somewhere in the middle, but if the Quote starts with my retailer name, it gives a false.

That changes the criteria.

This will work if your spacing format is consistent (i.e. there is not a dash directly next to the name):

=SUMPRODUCT(--ISNUMBER(SEARCH(" "&Retailers_Tracked&" "," "&A2&" ",1)/LEN(Retailers_Tracked)))>0

Give this a try
=SUMPRODUCT(--ISNUMBER(SEARCH("*"&Retailers_Tracked&"*",A9,1)/LEN(Retailers_Tracked)))>0

I can't control the data, that's my problem. We have dozens of quote specialists that are supposed to enter information in an exact way, but being human, they don't. And since I'm so far down the line from them they don't understand the impact of sloppy typing. Re-training doesn't work because they always revert back to bad habits. However, I have figured out that specific words come up a lot like Masonic and since Sonic is a retailer I get a false positive. So I created something like this to work around the known problem words and I figure I'll just have to keep updating the spreadsheet, but I was hoping I could just have one formula that adds the two formulas into one.

Try this:

=SUMPRODUCT(--ISNUMBER(SEARCH(" "&SUBSTITUTE(Retailers_Tracked,"-","")&" "," "&SUBSTITUTE(A2,"-","")&" ",1)/LEN(Retailers_Tracked)))>0

This is SOO close to perfect! But I've found 2 false positives due to spacing or the lackthereof. I have McDonald's that has a ( in front with no spacing and a hyphen in another word with no spacing. Honda-Penske is showing false when it should be true. Can you adjust for that?

If you update your sample from post #3 to include the new search words and strings that are not working as expected, I'll take a look.

Thank you.

It's getting ugly, but this worked for the sample given in post #10:

=SUMPRODUCT(--ISNUMBER(SEARCH(" "&SUBSTITUTE(Retailers_Tracked,"-"," ")&" "," "&SUBSTITUTE(SUBSTITUTE(A3,"-"," "),"("," ")&" ",1)/LEN(Retailers_Tracked)))>0

Ugly? It's a thing of beauty! It's absolutely perfect and you've made my day, my weekend and my next week. My boss is so thrilled with me and you and wanted me to tell you THANK YOU and if we could rank you on Yelp you'd get 5 stars!

Thank you so much for your help, patience and sharing your incredible skill.

Great, happy to help!