Ok here is my suggestion for what you are trying to do. Normally I would write a VBA script to do this but it is very possible to do with formulas as well.
1: I would change where you create the search name into column A on Sheet2. This makes it alot easier to use it for vlookups. You can still easily copy and paste new data in the sheet just start on Column B.
2: I would change your search name formula to the following
Code:
=SUBSTITUTE(LEFT(C2,FIND(",",C2)-1)," ","")
This removes the spaces which have been problematic the only thing to watch out for is duplicate first and last names.
3: Use the following Vlookup formula in Column I for Sheet1
Code:
=VLOOKUP(B4&A4,Sheet2!A:D,4,0)
With these changes this should work except for the names that have the dreaded * in them. This will need to be removed and a Find and Replace can be problematic with removing * because it is considered a wildcard.
If this still isnt quite what you are looking for maybe we should look into writing you a VBA script that will do this task automatically.
Let me know
Thanks