Test20Aug2014.xlsx

Ok thanks i tried that formula and it worked on most names, i think my problem might be related to the way i have to input the names in column A.
Ok here is a better way of letting you know what i have to do.

In column A and column B in sheet 2, i copy and paste these names from another site, and this is why the names have OF, RF, LF, 1B, 2B, 3B, SS, etc.
That is the problem i think but i thought the formulas in sheet 2 in columns C, D, E, F, would take of that, but it isn't i guess.
Here is a different sheet that is what it looks like after i paste the names in columns A, B, C.


Also in columns K, L, in sheet 2 is where i paste the info, and then repaste them in columns A, B, C, in sheet 2
I hope this didn't throw everything crazy.
Thanks for your help.

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
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
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