Partial text match question

[threebeltking]

I have these two sets of data referring to fruit. One of which is based on an extended sentence and has multiple entries per fruit, the other is a condensed version.

I need a way to link the two sets of Data. I can already match it by way of partial text match (=IF(Extended!A:A="*Condensed!A2*", then...)) but I need away to do the inverse of that. Trying to say "Search through Condensed!A:A and find which of those matches Extended!A2", rather than "Search through Extended!A:A and find which of those matched Condensed!A2". Thats a rough explanation but any ideas would help!

 

[davesexcel]

Why do you have orange as no?

 

[davesexcel]

=IF(OR(INDEX(COUNTIF(Extended!A1,"*"&Condensed!$A$1:$A$4&"*"),)), "Yes", "no")