Your data has ascii characters instead of spaces in some of the cells which makes look ups kind of weird. The formula below worked for every cell except for Dee Gordon who for some example has a differently formatted name. If you can change this it will work no problem. Also it appeared that certain names included OF rather than RF so the IF statement accounts for that.
Place the following code in Sheet1 cell I4 and autofill down.
Code:
=VLOOKUP(IF(RIGHT(E4,1)="F",B4&" "&A4&", "&F4&"*OF",B4&" "&A4&", "&F4&"*"&E4),Sheet2!A:B,2,0)
Let me know if that works out.
Thanks