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

hsandeep

Well-known Member
Joined
Dec 6, 2008
Messages
1,213
Office Version
  1. 2010
Platform
  1. Windows
  2. Mobile
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".
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
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?
 
Upvote 0
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.
 
Upvote 0
Someone please help me on my post #4. I really need it.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
Someone please lend help. I am doing vlookup & try to MATCH the 2 words in the 2 Excel workbooks.
 
Upvote 0
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.​
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,215
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top