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

    VLOOKUP NOT WORKING FOR ALL ITEMS (WINDOWS 2003)

    I cannot understand why the attached excel works for some VLOOKUP's and not for others. Any ideas???

    Thanks

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

    Re: VLOOKUP NOT WORKING FOR ALL ITEMS (WINDOWS 2003)

    Could you please stop using ALL CAPS in the subject? Thanks.

    Cell J8 contains "1011WC " i.e. there is a space after the text. Cell A8 contains "1011WC" i.e. no space after the text. So the value of J8 is not found. You'll have to make sure that either all entries have a trailing space, or none have.

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

    Re: VLOOKUP NOT WORKING FOR ALL ITEMS (WINDOWS 200

    Is there a way to ensure that there are no spaces after the lookup value?

    Thanks

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: VLOOKUP NOT WORKING FOR ALL ITEMS (WINDOWS 200

    You could select all the values then do a find-replace...

    Steve

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

    Re: VLOOKUP NOT WORKING FOR ALL ITEMS (WINDOWS 200

    Thanks for the tip.

    Is there a way to use the "Trim" function or something like it to eradicate the spaces at the end of the text so I could place it in a formula?

    Martin

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: VLOOKUP NOT WORKING FOR ALL ITEMS (WINDOWS 200

    Instead of usiing your current values in A, you could insert a new column and fill it with (in B5)
    =trim(A5)

    and copy it down the column

    And then use in L5 a formula like:
    =VLOOKUP(TRIM(J5),$B$5:$C$181,2,FALSE)

    Alternately you could use in L5 the array formula (must be confirmed with ctrl-shift-enter):
    =VLOOKUP(TRIM(J5),TRIM($A$5:$B$181),2,FALSE)

    If you don't need the values for anything (it will make the output trimmed text). if they are all numbers you could use the array formula (must be confirmed with ctrl-shift-enter):
    =VALUE(VLOOKUP(TRIM(J5),TRIM($A$5:$B$181),2,FALSE) )

    If it could be both text or numbers, i would go with the intermediate calcuation or just ensure the integrity of the data.

    Steve

Posting Permissions

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