Count the exactly the same words from set of words

Copper Contributor

Good day to Excel black belts! It would be really appreciated if you could help me or give me some hints regarding my issue.  It looks simple but turned out to be very complicated for me.  My file is here




As shown in the picture above, I want to put 1 in each of C5:F6 if attributes written in each of C4:F4 match one of the attributes written in B5:B6. The thing that makes this table complicated is that as "Cap" is an independent attribute from "Knitted Cap" and "White Cap", 1 should not be shown in C5. I would like to make Excel differentiate "Knitted Cap" and "Cap" so that  1 is not shown in C5.

In C5 I use formula  =COUNTIF($B5,"*"&C$4&"*") to count any of the attributes are included in the corresponding cell. You can see my file and formula here


If anyone could help me how to differentiate "Knitted Cap" with "Cap", or any suggestion to change formula / cell structures, that would be really appreciated.


Thank you very much.


2 Replies
best response confirmed by Hans Vogelaar (MVP)

@yoshiwata Perhaps the easiest way out, though perhaps not practical,  would be to be aware of the instances that one keyword may be part of another keyword up front and slightly change such keywords. For instance, change "Cap" to "Cap_" and your formula will work.

If your real life problem is more complex than your sample, you may want to consider PowerQuery (PQ) as demonstrated. Then you can easily avoid the matching of Cap with White Cap. 

I haven't looked into a formula based solution as it it so easy with PQ. Please see the attached file.

Thank you so much! I will consider PQ as it brings a perfect result.