Match 2 cells with names with MAXIMUM accuracy as defined & how to do vlookup

[hsandeep]

I am doing vlookup_value on Names in workbook1 column X5:X9999 with table_array in another workbook2 column E5:E9999.
Names contains Maximum 4 words of alphabets & each word separated by a blank. But due to punching error there has been slight variation in the spelling & so I am unable to perform vlookup.
Is there any method which can be used so that the names are MATCHED even if slight variation is there. I have been trying with different ideas but not zeroing down to one idea. Any help?
Ex:
Workbook1
X5=PETER G ENGLISH COMMANDAR
Workbook2
E5=PETER G ENGLIS COMMANDER
These 2 names are of same identity & hence it has to be accepted.
Can it be made like: ONLY 1 letter per word is allowed as mis-match with minimum as 1
So, in PETER G ENGLISH COMMANDAR 5 1 7 9 letters are there. So 4 1 6 8 letters in SEQUENCE matches the vlookup should work else "NO MATCH".

 

[Robert Mika]

Try here:
http://www.mrexcel.com/forum/excel-questions/195635-fuzzy-matching-new-version-plus-explanation.html

 

[hsandeep]

Robert,
I read the thread. But please excuse me since I was not able to implement it practically. I am trying to match NAMES in the 2 workbooks. The names may be 'slightly' different but still they need to be matched. What logic should I apply?
In my above example: 1 alphabet in the 3rd word is missing ENGLISH(ENGLISH & ENGLIS) & 1 alphabet in the 4th word is different (COMMANDAR
& COMMANDER). Still the answer should be MATCHED. How to accomplish?

 

[hsandeep]

I think I have an approach but do not know the formula.
There are maximum 4 words & each word separated by either a blank, "-" or ".".
NAME=FIRST SECOND-THIRD FOURTH
For EXACT MATCHING: FIRST word should MATCH EXACTLY & ALL letters.
SECOND & THIRD: 1st 2 alphabets & last 2 alphabets should MATCH EXACTLY.
LAST (FOURTH if 4 words): 1st 3 alphabets & last 3 alphabets should MATCH EXACTLY.

Ex:
Workbook1 (Input)
X5=PETER G ENGLISH COMMANDAR
Workbook2 (Output)
E5=PETER G ENGLIS COMMANDER
PETER G ENGLISH COMMANDAR 5 1 7 9 letters are there. So for MATCHING: PETER G (EN & SH) (COM & DAR) should MATCH EXACTLY.
Answer: If MATCHED then "MATCHES" else "DOES NOT MATCH"
I am doing vlookup.

 

[hsandeep]

Someone please help me on my post #4. I really need it.

 

[hsandeep]

Please someone help in my thread#4. Any clarification needed I will provide immediately.
Rule for MATCHING is:
FIRST word: should MATCH EXACTLY & ALL letters.
SECOND & THIRD words (if present): FIRST 2 alphabets & LAST 2 alphabets should MATCH EXACTLY.
LAST (FOURTH if 4 words): FIRST 3 alphabets & LAST 3 alphabets should MATCH EXACTLY.

There are MAXIMUM 4 words in the name.

 

[hsandeep]

Name=Maximum 4 words & each word with maximum 15 letters
Name1=ABCDEF ADFTYZ GHIJKLMN UVRSTUVWXY
NAME2=ABCDEF ADSSYZ GHSSDDMN UVRSSSDDDSSSWXY
Answer should be MATCHED since:
FIRST word: MATCHED EXACTLY & No. letters (6 in both).
SECOND word (if present): FIRST 2 alphabets & LAST 2 alphabets MATCHED EXACTLY.
THIRD word (if present): FIRST 2 alphabets & LAST 2 alphabets MATCHED EXACTLY.
LAST (FOURTH if 4 words): FIRST 3 alphabets & LAST 3 alphabets MATCHED EXACTLY.

Now please give the formula.

 

[hsandeep]

Someone please lend help. I am doing vlookup & try to MATCH the 2 words in the 2 Excel workbooks.

 

[hsandeep]

Someone please help me on my post #4. I am doing vlookup but don't know how to solve it? NAMES in 2 workbooks are to be matched & criteria for matching I have defined in sr. no. 4 onwards. Please HELP.​