Page 1 of 2 12 LastLast
Results 1 to 10 of 11

vlookup with text data

This is a discussion on vlookup with text data within the Excel Questions forums, part of the Question Forums category; I am having a problem using text data with the vlookup function. The lookup value is in text format and ...

  1. #1
    New Member
    Join Date
    Mar 2002
    Posts
    6

    Default

    I am having a problem using text data with the vlookup function. The lookup value is in text format and the first column of the data range is in text format. If I copy and paste the lookup value into the lookup value cell location, I get #N/A as a result of my vlookup function, even though the lookup value is in the first column of my data range.

    If I type the lookup value into the the lookup value cell location, the function works fine. This isn't a solution that I can use, because I have a list of items to lookup and it would take too long to copy and paste the values and retype these values in the same cell.

    Has anyone encountered this issue before?

  2. #2
    IML
    IML is offline
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Denver, CO
    Posts
    1,744

    Default

    On 2002-03-27 09:18, jcmeyer wrote:
    I am having a problem using text data with the vlookup function. The lookup value is in text format and the first column of the data range is in text format. If I copy and paste the lookup value into the lookup value cell location, I get #N/A as a result of my vlookup function, even though the lookup value is in the first column of my data range.

    If I type the lookup value into the the lookup value cell location, the function works fine. This isn't a solution that I can use, because I have a list of items to lookup and it would take too long to copy and paste the values and retype these values in the same cell.

    Has anyone encountered this issue before?
    what are the results of the =ISNUMBER()
    formula, using the first value in your look up table, and the value you want to look up?

    If they are both text, the results should both be false. A real "number" can reside in a cell formatted as text.

  3. #3
    New Member
    Join Date
    Mar 2002
    Posts
    6

    Default


  4. #4
    New Member
    Join Date
    Mar 2002
    Posts
    6

    Default

    The rsults of both isnumber() formulas is false. Does that mean the vlookup function won't work for this data?

  5. #5
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    61,972

    Default

    On 2002-03-27 09:33, jcmeyer wrote:
    The rsults of both isnumber() formulas is false. Does that mean the vlookup function won't work for this data?
    Check also whether the lookup value and the same value in the table do have the same length:

    =LEN(lookup-value)=LEN(the-same-entry-in-the-first-col-of-the-lookup-table)

    because of extraneous spaces.

  6. #6
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454

    Default

    Hi jemeyer:
    How about if you post some of your sample data, and the formula you are using -- that will be a big help in looking at what you are working with and where is the problem. It appears you may have a syntax problem in using the VLOOKUP function!
    Regards!

    Yogi Anand, D.Eng, P.E.
    Energy Efficient Building Network LLC
    www.energyefficientbuild.com

  7. #7
    New Member
    Join Date
    Mar 2002
    Posts
    6

    Default

    The lengths of both values are the same.

  8. #8
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    61,972

    Default

    On 2002-03-27 09:41, jcmeyer wrote:
    The lengths of both values are the same.
    =COUNTIF(A1:A10,lookup-value)

    where A1:A10 is the first column range of your lookup-table.

    What do you get?

    As Anand suggests, post also your VLOOKUP formula in the follow-up.

  9. #9
    New Member
    Join Date
    Mar 2002
    Posts
    6

    Default

    Yogi,

    Would you rather I sent you the file by email. It is not a very big file.

    Thanks,

    John

  10. #10
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454

    Default

    On 2002-03-27 09:53, jcmeyer wrote:
    Yogi,

    Would you rather I sent you the file by email. It is not a very big file.

    Thanks,

    John
    Hi jcmeyer:
    Yes you can email it to me -- if you like -- or post it on the board!

Page 1 of 2 12 LastLast

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