Search and match

[demonfootball21]

I have a spreadsheet all calculated and all my formulas work i have just run into one problem.

i have three sheets. Sheet A, B, C...my master A, has all my info that I do no touch. has every possible data(in this case names of students and their info) listed.

in sheet B is the new sheet i get everyweek that only populates names(last(Column A), and first(Column B)) this sheet changes weekly and can vary in length.

my third sheet has all my forumlas. in Column A it is just pulling from sheet B with last names. In column B has first names. Now for columns C - AW i am using the lookup function. I take the name in column "A"(last name) and search through Sheet A. from there i populate all their info that matches from C to AW(basically a copy and paste function from one sheet to the next)

the pronblem i run into is when there are two names that are the same.

Sanders, Deion - 10.20.1983 (sheet searches for Sanders and matches his birday perfectly)
Smith, Jon - 11/2/1990 (sheet searchers for SMith and matches his birthday perfectly)
Smith, Bob - 12/1/1990 (sheet searches for Smith and matches 11/2/1990 as it doesnt get past Jon!)

anyone know of a better function to use? MrExcel sent me to his podcast from Friday but it didnt seem like that was what i wanted? thanks!

 

[demonfootball21]

The names have to be seperated to A and B columns because i have to merge this preadsheet to another that is not mine and that i can not change. So A has to be last name and B has to be first