To find key words in name to be able to match to list

[Shamz41]

Hi

I have 2 lists of name and color types. The issue i have is I may not have the full name of the person or the names may be in jumbled up format. I need column E to be able to trace the color types if there is a similarity of key words in column A and column D. I have added some samples to show expected answers.

[FlameRetired]

In the attached please find helper column F. This formula counts the number of "names" in each row of column A.

Please Login or Register  to view this content.

Then in E3 and filled down.

Please Login or Register  to view this content.