Need formula to search a column for names where not always listed the exact same as lookup

So what I am looking for is a formula that will search a column for a name where the lookup value could have an additional name in it (in addition to first and last). The lookup location is not always the exact same. I am attaching a sheet with notes to help see what I am trying to do. I could not get VLOOKUP to work or index match seeing this name is not always the same. Maybe including SEARCH with it will help, I'm stuck?

Any help is much appreciated!!