Wildcard solution maybe??

[bc4240]

Each week I have to scan a changing list of names that includes former students. I want to find each student listed. My problem is that the list is outside my control and sometimes students names are presented differently depending on who creates that weekly list. Often Thomas becomes Tom, Nathan becomes Nate, Robert becomes Bob. Is there a way to wildcard any/all of the matching letters in my Cross Check list to the Weekly list e.g.

Weekly Cross Check Solution
Robert Bob (Since B and O are in both I'd like Robert to show in my Solution cell)
Thomas Tom (Since T, O and M are in both I'd like Thomas to show in my Solution cell)
Nate Nathan (Since N, A and T are in both I'd like Nate to show in my Solution cell)

This is as close as I have come to a solution. I am currently using this in my Solution cells:

=XLOOKUP("*"&LEFT(Weekly Range,3)&"*",Cross Check Range,Weekly Range,"No Match",2)

But this basically looks at the Cross Check Range cell's first three letters with anything before or after and compares those three letters to the Weekly Range Values. Because there is no Bob in Robert or there is no Tom in Thomas I get a "No Match". There is a "Nat" in Nate so I get a positive return of Nate.

Thanks for any suggestions in advance.

 

[bc4240]

Sorry my typing above got crunched together