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

apart from "your seperation leaves a blank space in middle name" this is my vlookup formula fine


I have tested the trim formula as well but it is not working could you please provide more suggestions

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

also I tested VLOOKUP($D2,Data1!$E$2:$G$36,2,FALSE)

=trim(G2) which is the result VLOOKUP($D2,Data1!$E$2:$G$36,2,FALSE)


Can you provide me the formula so I can test it on it. See attached spreadsheet for more infomation.

Thanky you

Thanks mate, I got it now. Trim function is working now and gives me some result now This is the formula I was using to extract middle name from full name, I wasn't using trim function so there was extra space comingat the end of the name so I trim it n it working now

=TRIM(CONCATENATE(IF(ISERR(MID($A28,FIND(" ",$A28)+1,IF(ISERR(FIND(" ",$A28,FIND(" ",$A28)+1)),FIND(" ",$A28),FIND(" ",$A28,FIND(" ",$A28)+1))-FIND(" ",$A28)-1)),"",MID($A28,FIND(" ",$A28)+1,IF(ISERR(FIND(" ",$A28,FIND(" ",$A28)+1)),FIND(" ",$A28),FIND(" ",$A28,FIND(" ",$A28)+1))-FIND(" ",$A28)-1)), " ",(IF(ISERR(MID($I28,FIND(" ",$I28)+1,IF(ISERR(FIND(" ",$I28,FIND(" ",$I28)+1)),FIND(" ",$I28),FIND(" ",$I28,FIND(" ",$I28)+1))-FIND(" ",$I28)-1)),"",MID($I28,FIND(" ",$I28)+1,IF(ISERR(FIND(" ",$I28,FIND(" ",$I28)+1)),FIND(" ",$I28),FIND(" ",$I28,FIND(" ",$I28)+1))-FIND(" ",$I28)-1)))))


But still there is a problem of employees either having same first name or same last name or same middle name.

so far my vlookup formula is running on middle name and looking in the range of first name, middle name and last name it is still not able to recognise the correct result

because two people have different last name n first name but same middle name. So it is difficult to trust the results.

Is there a way to tie up ur vlookup formula to look up middle name in the lookup range and check there corresponding first name & last name match it then give me the result as middle name to the correct first name n last name.

How to match middle name from one set of data to first name & last name of other set of data and check if there middle name is same and matching first name & last name too then provide there middle name as a outcome.

How it will possible using vlookup or any other function available in excel


Thank you

Yes you are right if you find a middle name, lookup first name and surname as well and returns the corresponding matching middle name.

What do you meant by design? Do you mean the names yes if yes it is by design.

Also, I have attached the updated spreadhseet for your reference.

What I want to achieve is this for EG

Data set 1
First Name Mid Name Last Name
Peter Ruse Parker
Chris Dave Mackay
Robert Ruse Stent


Data set 2

First Name Mid Name Last Name
Chris Dave Mackay
Peter R Parker
Robert Ruse Stent


Now when you do a vlookup based on my spreadsheet on data 1 to lookup mid name in data2 it gives me a result below

Mid Name(result) Data set 2 First name last name
Ruse Chris Mackay
R Robert Stent


The above result is wrong as per my data Robert stent has prpoer mid name as Ruse in both data sets where as Peter Parker doesn't and in this result there is nothing for Peter Parker so if I have 100 of line of rows of data then it is hard to trust the accuracy and result of the formula

What I want is simply show me

Mid Name(result) First name last name
Dave Chris Mackay
Ruse Robert Stent
#N/A Peter Parker

so that I know peter mid name doesn't match and then i can filter the result and update our records.

This is possible or am I looking for something that is out of the box.

Thanks

Yes it works fine. I have made couple of changes to your formula by removing some spaces, and expanding lookup range to get mid name as result. The result showing #N/A means no matching record found is that right? so that I can filter on it and update the records.

Please check the attached spreadsheet with change formula.

Thanks for your help and support, much appreciated.