Need a Vlookup formula to search based on part of a cell

Hey all, so I am trying to write a VLOOKUP (or anything that will get me where I need to be) and I cannot find what I need to make it work. Basically I have 2 lists on 2 different sheets. On Sheet 1 I have a list that in column A contains cells with multiple bits of information (D1.DC - David Ensor) and the only part that I actually care about is the name (David Ensor). On Sheet 2 I have a list of names in column A showing only the name (David Ensor) and column B has numbers in it associated with that person. What I need is a formula that will look at D1.DC - David Ensor and find David Ensor in Sheet2 and give me the number beside it. I can make it work if I do the lookup on just the name and it will find his name at the end of the string, but I really need it to go the other way. Unfortunately the list of people that I get sent (list from Sheet1) is not laid out the same other than the fact that the person's name is always there at the end, sometimes there are no spaces and sometimes there are no hyphens. I am trying to make my report so that i can just dump the list of names they send me each week into the sheet and pull the data from my master list (Sheet2 info). I hope this makes sense and someone can help me. It seems to me like this should be simple but I just cannot seem to get it figured out. Thanks so much for taking the time to read this. Basically what I need is below and I have attached a sample of what I am looking at.

Sheet1, Cell A1 = D1.DC - David Ensor
Sheet2, somewhere in the Column A list (A71) = David Ensor
Sheet2, Column D next to David Ensor (D71) = 5
Sheet1, Cell C1 needs to = 5 (Sheet2 Cell D71)

Please take a moment to re-read forum rule #1 and then amend your thread title to something that better explains your problem. Changing your thread title is not optional, which means you must change it. Thanks!

• Use concise, accurate thread titles.
  
• Your post title should describe your problem, not your anticipated solution.
• Use terms appropriate to a Google search - poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice tell us nothing.
• Responding to a request to change your thread title by doing so is mandatory.

To change a title go to your first post, click EDIT then Go Advanced and change your title.

No help to be offered, please, until the OP complies with this request.