Thanks:  0
Likes:  0

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

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

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

3. ## Re: Match 2 cells with names with MAXIMUM accuracy as defined & how to do vlookup

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?

4. ## Re: Match 2 cells with names with MAXIMUM accuracy as defined & how to do vlookup

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.

6. ## Re: Match 2 cells with names with MAXIMUM accuracy as defined & how to do vlookup

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.

7. ## Re: Match 2 cells with names with MAXIMUM accuracy as defined & how to do vlookup

Name=Maximum 4 words & each word with maximum 15 letters
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.

8. ## Re: Match 2 cells with names with MAXIMUM accuracy as defined & how to do vlookup

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

9. ## Re: Match 2 cells with names with MAXIMUM accuracy as defined & how to do vlookup

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.

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•