Results 1 to 9 of 9
  1. #1
    3 Star Lounger
    Join Date
    Jun 2005
    Posts
    386
    Thanks
    3
    Thanked 0 Times in 0 Posts

    VLOOKUP ITEM (XP & 2003)

    The attached has a vlookup formula that for some reason only is working for a few lookups. Could you help me on this? I cannot find the problem nor the solution.

    Thank You

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VLOOKUP ITEM (XP & 2003)

    Some of the strings in both column A and Column F have a trailing space. The VLOOKUP is only going to work when they both either have the space of don't have the space.
    Legare Coleman

  3. #3
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: VLOOKUP ITEM (XP & 2003)

    Hi There

    There is a bit of data cleansing you require for your data, I used the trim formula and then cut and paste specialled. Once I cleaned up all the dodgy codes I used this function in B4 =INDEX(myRange,MATCH(A4,myCodes,FALSE),2) and the copied down

    I always use named ranges in workbooks with long list as it helps with resources.

    Have a look at the attached cleansed wb
    Jerry

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: VLOOKUP ITEM (XP & 2003)

    This seems to be the same problem as in the threads starting at <post:=602,723>post 602,723</post:> and at <post:=601,572>post 601,572</post:>. Same cause too. And same remark about the subject - again.

  5. #5
    3 Star Lounger
    Join Date
    Jun 2005
    Posts
    386
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: VLOOKUP ITEM (XP & 2003)

    Thanks for the info.

    one more question.What does the phrase mean at the end of your reply. (Latin i think)

    Thanks again

  6. #6
    3 Star Lounger
    Join Date
    Jun 2005
    Posts
    386
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: VLOOKUP ITEM (XP & 2003)

    Thanks

  7. #7
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: VLOOKUP ITEM (XP & 2003)

    >What does the phrase mean at the end of your reply

    All my quotes are listed in my Control Panel as a FAQ. It is latin and it means:


    In the good old days, children like you were left to perish on windswept crags.
    Jerry

  8. #8
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    199
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: VLOOKUP ITEM (XP & 2003)

    Hi,

    Another way of addressing this problem without the chore of having to "clean" the data is to modify the Lookup_Value so that it is more flexible. In the first VLookup change the A4 to "*"&A4&"*" and then copy down and the problem is solved!

    Good Luck!

    Peter Moran

  9. #9
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: VLOOKUP ITEM (XP & 2003)

    Peter

    That is an excellent point, I have seen other similar answers but using TRIM for MNN's problem. The secret is being aware that there is a problem with rogue characters in the data in the first place and your idea is a good contingency.
    Jerry

Posting Permissions

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