Lookup list within cell and Compare to multiple columns

[MHamid]

Hello,

I am looking for a formula that will do the following.
I have a column (let's call it column B) that has a list of businesses separated by semicolons. I want to look at every single one of these businesses within the cell and check to see if it is either in column C or D (Primary Business or Secondary Business) of that row. If the business is found in either columns C or D, then say YES. But if there are any businesses missing, then say NO and notate which business is missing. Also, the other way around. Is the Primary and Secondary Businesses listed in the Grouped Businesses. If Yes, then say Yes. If not, then say Yes, but {name of business} is not in Grouped Businesses.
See sample below.

Is it possible to have a formula get the results shown in Column E?

Account#	Grouped Businesses	Primary Business	Secondary Business	Are Grouped Businesses in either the Primary Business or the Secondary Business Columns & vice versa (Y/N)
123456789​	Technology; Compliance; Operations; AML	Operations	Technology/Compliance/AML	Yes
987654321​	Technology	Technology		Yes
234567890​	Technology; Compliance; Operations; AML	Technology	Compliance	No, Missing Operations and AML
987654320​	Technology	AML		No, Missing Technology
345678901​	Technology; Compliance; Operations; AML	Finance	Technology/Operations/AML/Compliance	Yes, Finance is not in Grouped Businesses
109876543​	Technology	AML	Finance	Yes, Finance is not in Grouped Businesses

Thank you

 

[MHamid]

I've actually noticed it for data in Column C only. So what I need is that if column C shows blank or "Multi Business" just ignore those.

Grouped Businesses	Primary Business	Secondary Business
Technology	Multi O&T Businesses	Technology	YES |
Operations	Multi O&T Businesses		No, Missing Operations |
Technology; Compliance; Operations; AML		Compliance	No, Missing Operations, Technology, Operations, and AML |

I'm looking at the data now and I think I actually may need to split this up into two different columns instead. I think the above results may confuse people.
It will be same the idea so I will keep this post and just add another reply with new requirements.