How do I use Excel's "=SUMPRODUCT(--ISNUMBER(SEARCH..." function to exclude 'complicated compounded' results?

Question

I am trying to validate a list of product descriptions to highlight any instances where the colour of the product has been used in its description.

I have a list of colours...

Example:
Blue
Red
Green
Yellow
Purple
etc.

I am using the following formula to detect if one of these colours has been used:

=IF(SUMPRODUCT(--ISNUMBER(SEARCH(Table1[Colors],A1)))>0,"Cannot include a colour","")

Where Table1[Colours] contains my list of colour text strings and A1 contains my first product description.

(Dave Bruns @ ExcelJet has a great read for anyone wanting to use SUMPRODUCT/ISNUMBER/SEARCH combinations.)

If a product description contains a colour specified in my Table1[Colours] list the formula produces "Cannot include a colour" to remind the user this is not allowed.

Example: "Garmin Forerunner 10 Running Sportswatch Green"

The first issue i am faced with is that my current formula procs when the product description contains a compound word containing a colour...

Example: "Blackberry Z10 Smartphone"

This inaccurately invalidates the description because the string "Black" in this text is not being used to describe the colour of the product.

As the title suggests, my main issue lies with outsmarting 'complicated compound' words...

... my Table1[Colours] list does not simply contain the basic Primary, Secondary and Tertiary colours, but also more exotic ones like Coral, Fuchsia and Tan.

This causes complication when the product description contains a word like "Stand".

Why is this a problem you may be thinking? Stand contains one of my exotic colours 'Tan'

S-Tan-d

Unfortunately this also causes my formula to proc. (Annoying right?)

The solution I am looking for is an addition to my existing formula =IF(SUMPRODUCT(--ISNUMBER(SEARCH(Table1[Colors],A1)))>0,"Cannot include a colour","") which accounts for the possible occurrence of a 'complicated compound' be this by a counter list of acceptable words (e.g. Table2[Exceptions] or by wild carding the search to match the exact colour with no Prefix or Suffix (this option would have to allow for the possibility of a dual colour separated by a / e.g. "Black/Red", so wild carding with certain punctuation exceptions?)

...Its all just a bit horrible and inconvenient.

Any advice is appreciated.

Thanks, Mr. J

Answer 1

=SUMPRODUCT(  ($G$2:$G$159 >= $E$164) *  ($G$2:$G$159 <= $F$164 ) * (EXACT($E165,$F$2:$F$159)))

Better use "EXACT" instead of is number