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

    VLookup with text as references (XP & 2003)

    Subject edited by HansV - all caps comes over as shouting.

    I have a worksheet(ATTACHED) with a VLOOKUP table, where the key references are text not numbers. All I am getting as a result is tne #N/A . How could I accomplish my goal in this situation

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

    Re: VLookup with text as references (XP & 2003)

    One problem is that many values in column A are followed by a trailing space, while the corresponding values in column G aren't. You can get around this by using TRIM:

    =VLOOKUP(TRIM(A4),$G$4:$H$87,2,FALSE)

    but you'll still get many #N/A values, since a lot of the values in column A have no match in column G, even after removing trailing spaces.

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

    Re: VLookup with text as references (XP & 2003)

    Thanks Mr. V. , I realize that column could have the #n/a under certain situations. thanks again
    Martin

  4. #4
    Star Lounger
    Join Date
    Nov 2003
    Location
    Germany
    Posts
    75
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VLookup with text as references (XP & 2003)

    Or if you don't want to show the #N/A at all (using Hans' formula):

    =IF(ISNA(VLOOKUP(TRIM(A4),$G$4:$H$87,2,FALSE)),"", VLOOKUP(TRIM(A4),$G$4:$H$87,2,FALSE))

    You may replace the "" in the middle by eg "no value" or other text which may suit you.

    Wolf

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

    Re: VLookup with text as references (XP & 2003)

    Nice finishing touch,

    thank you

Posting Permissions

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