Vlookup or any other function to find, match and provide exact middle name from data

Hi I am trying to write a vlookup formula to find and match middle name of an employee from two different sets of data.

=VLOOKUP($D2,Data1!$E$2:$G$36,2,FALSE)


But no luck please see if I am doing anything wrong or there is a better way of doing it with some other functions in excel 2010. My formula is providing results but it not able to match the data properly.

For ex - I have an employee whose name is Alan Peter Parker and there is another employee whose name is Dave Peter Patrick (just an example so pls ignore me for wrong combination of first & last name)

No I have used LEFT, Right & Mid functions to separate data into First, Middle & Last name in 3 columns

Now I have another set of data from different systems where the above employees have been setup too with the same names, the only difference is that Alan Peter Parker is setup as Alan Parker.

Now I am trying to figure out a formula using vlookup, index & match functions to try find Peter as middle name in these two set of data so that I can filter then and check which one's are missing there middle names so that we can update the data in the system.

Now the problem is coming is when I do my vlooup on middle name = Peter and lookup range = first name, last name, middle name & retun middle name as a result it gives me #N/A (might be bcoz of missing middle name) and the other cell says Peter but in front of Alan as it can check whether Peter is with Alan or Dave .


=VLOOKUP($D2,Data1!$E$2:$G$36,2,FALSE)


How to over come this issue.

Excel spreadsheet attached to play with it.

Thank you




Vlookup or any other function to find, match and provide exact middle name from two different set of data.