Results 1 to 9 of 9

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

This is a discussion on Match 2 cells with names with MAXIMUM accuracy as defined & how to do vlookup within the Excel Questions forums, part of the Question Forums category; I am doing vlookup_value on Names in workbook1 column X5:X9999 with table_array in another workbook2 column E5:E9999. Names contains Maximum ...

  1. #1
    Board Regular
    Join Date
    Dec 2008
    Location
    Mumbai
    Posts
    577

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

  2. #2
    MrExcel MVP Robert Mika's Avatar
    Join Date
    Jun 2009
    Location
    (current) UK, (origin) Poland
    Posts
    7,217

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

    - Read the Posting Guidelines, Forum Rules & FAQs
    - Want to post a small screen shot? Try one of these Excel jeanie, MrExcel HTML Maker or Borders-Copy-Paste
    - If posting vba code, please use Code Tags .

  3. #3
    Board Regular
    Join Date
    Dec 2008
    Location
    Mumbai
    Posts
    577

    Default 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. #4
    Board Regular
    Join Date
    Dec 2008
    Location
    Mumbai
    Posts
    577

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

  5. #5
    Board Regular
    Join Date
    Dec 2008
    Location
    Mumbai
    Posts
    577

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

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

  6. #6
    Board Regular
    Join Date
    Dec 2008
    Location
    Mumbai
    Posts
    577

    Default 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. #7
    Board Regular
    Join Date
    Dec 2008
    Location
    Mumbai
    Posts
    577

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

  8. #8
    Board Regular
    Join Date
    Dec 2008
    Location
    Mumbai
    Posts
    577

    Default 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. #9
    Board Regular
    Join Date
    Dec 2008
    Location
    Mumbai
    Posts
    577

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

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com