If a cell has a partial text match to a list of text, then "yes"

[RedBlossom]

Hello,
I have been trying to figure out a formula for a couple hours now and am having trouble. I have a list of individuals and a list of schools (See example below) on two separate sheets. I also have a third sheet that includes name, some other information, and I need to include whether or not they attended a school on the list 2. I think the biggest problem I have is that partial matches are fine. For example, it says Suzie attend Chicago Booth, but List 2 only has University of Chicago. This needs to be "yes" but every time I try to do a vlookup or match, it is listed as "no" I'm guessing because it is not an exact match. Any help would be greatly appreciated. Thanks so much!

List 1:

NAME	SCHOOL
sam	harvard
joe	wisconsin
alan	ohio
suzie	Chicago Booth
pam	Iowa

<tbody>
</tbody>

List 2:

LIST OF SCHOOLS

harvard

MIT

University of Chicago

Oxford

<tbody>
</tbody>

 

[svendiamond]

Are you saying you want to return a "yes" if any of the text in Sheet1 column B matches any of the text in Sheet2 column A? That will be a hard rule to follow... as many of the college names will use the same letters and maybe the word "of", etc etc... so what exactly do you want to match here? Entire words? What if there was a "Harvardoulpe" college... should that match for Harvard? Should "Emmitt College" match for MIT? Iowa State for Iowa?

 

[RedBlossom]

Are you saying you want to return a "yes" if any of the text in Sheet1 column B matches any of the text in Sheet2 column A? That will be a hard rule to follow... as many of the college names will use the same letters and maybe the word "of", etc etc... so what exactly do you want to match here? Entire words? What if there was a "Harvardoulpe" college... should that match for Harvard? Should "Emmitt College" match for MIT? Iowa State for Iowa?

Hhhmm,
It needs to include the "main" name of the school. So from your examples, Harvardouple doesn't need to match for Harvard, but Harvard Business School does.

 

[svendiamond]

So "Iowa State" would work for "Iowa"? Just trying to figure out the best approach here. Can you upload your workbook or maybe a screenshot to see what everything actually looks like - specifically with your expected results in an extra column so we can see exactly what you're expecting each example to find and match. Thanks!