Results 1 to 6 of 6
  1. #1
    Lounger
    Join Date
    Dec 2009
    Location
    Belo Horizonte, Mnas Gerais, Brazil
    Posts
    27
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Question VLOOKUP accepts only typed values

    Hi buddies. I need help.
    I received a spreadsheet (Excel 2007) with the VLOOKUP function.
    The problem is that I only can input the “vlookup_value” by typing.
    If I paste the value, it is not recognized even if I paste as “value only”.
    I have to input almost 1000 values...
    Both cells, source and target, are in the TEXT format.
    TIA

  2. #2
    5 Star Lounger
    Join Date
    Dec 2009
    Location
    East Coast, USA
    Posts
    993
    Thanks
    8
    Thanked 43 Times in 43 Posts
    If possible, please attach a sample.

  3. #3
    Lounger
    Join Date
    Dec 2009
    Location
    Belo Horizonte, Mnas Gerais, Brazil
    Posts
    27
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Sending spreadsheets

    OK.
    I’d attached 2 spreadsheets: SOURCE and DESTINY.

    In SOURCE if you copy the B6 or B7 cell value and paste it in any DESTINY B3:B7 interval cells there will be an error message instead of the correspondent value, even if you use PASTE SPECIAL and the VALUE option.
    But if you 7515, the SOURCE cell value, it will work fine.
    I’d eliminated a lot of rows and columns in order to downsize the attachments.
    All the cells involved are in TEXT format.

    I will appreciate your help.

    Luiz
    Attached Files Attached Files

  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
    The values in that column are stored as text, not numbers and your lookup is text (as you indicate)

    When you paste the text, however, excel is converting it to a number and since the number is not found, it gives the error. If you change your formulat to make it look it up as text:
    =VLOOKUP(""&B3,$L$11:$M$34,2)

    It should work with it either way...

    Steve

  5. The Following 2 Users Say Thank You to sdckapr For This Useful Post:

    lhbmiranda (2011-05-02),Sox (2011-05-05)

  6. #5
    Lounger
    Join Date
    Dec 2009
    Location
    Belo Horizonte, Mnas Gerais, Brazil
    Posts
    27
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Thumbs up

    Quote Originally Posted by sdckapr View Post
    The values in that column are stored as text, not numbers and your lookup is text (as you indicate)

    When you paste the text, however, excel is converting it to a number and since the number is not found, it gives the error. If you change your formulat to make it look it up as text:
    =VLOOKUP(""&B3,$L$11:$M$34,2)

    It should work with it either way...

    Steve
    EXCELLENT Steve!
    It worked fine.
    Thanks a lot.

  7. #6
    New Lounger
    Join Date
    May 2011
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts
    It is often preferable to add ",false" at the end of the vlookup formula to avoid unintended results. The source data does to need to be alphabetical and it forces an exact match.

Tags for this Thread

Posting Permissions

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