Results 1 to 4 of 4
  1. #1
    Lounger
    Join Date
    May 2008
    Location
    Iowa, USA
    Posts
    29
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I get my data from two different programs. When I try to do a VLOOKUP on the information it does not recognize that the names that I am referring to from one spreadsheet to another are the same name. It has to have something to do with the formatting that I can not see or change. For example, Duke Animal Clinic in Sheet 1 is not the same as Duke Animal Clinic in Sheet 2. Is there a way to work through this?


    At a loss of what to do now...
    R
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    The names in Sheet1 have a space after them, while those in Sheet2 don't. You could get around this by deleting the trailing spaces, or else by using the following array formula (confirm with Ctrl+Shift+Enter) in cell B3 on Sheet2:

    =VLOOKUP(A3,TRIM(Sheet1!$A$3:$B$23),2,0)

    Fill down as far as needed.

    There are also names that are really different. For example, "Equine Veterinary Services (Dr. Garfinkel) " on Sheet1 is probably the same as "Equine Vet Serv Garfinkel" on Sheet2, but a formula obviously won't see them as the same.

  3. #3
    3 Star Lounger
    Join Date
    Nov 2002
    Location
    New York, New York, USA
    Posts
    266
    Thanks
    0
    Thanked 19 Times in 19 Posts
    For future reference.

    Excel has a function that you can use to compare two text strings and determine if they match.

    The function is EXACT(Text1, Text2) Text1 is the first text string and Text2 is the second text string.

    Exact is case sensitive but ignores formatting differences.

    If the strings are exact the function will return TRUE otherwise is will return FALSE.

    This can be a real help when your trying to determine why a complex formula is not working.

    In your above case EXACT would be FALSE showing you that there was some extra space or other character in one text when compared to the other text.


    Regards,

    Tom Dutie

  4. #4
    Lounger
    Join Date
    May 2008
    Location
    Iowa, USA
    Posts
    29
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thank you to both of you. The TRIM funtion worked well for me and lord knows I could of used that about a year ago. Also the EXACT funtion is a very "Good to know" one and I have printed this off for my reference later.

    What would I do without you guys?!?!?
    R

Posting Permissions

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