Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 14

Thread: Vlookup Help

  1. #1
    Board Regular
    Join Date
    Jan 2004
    Posts
    143
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Vlookup Help

    I am trying to lookup a value in a separate workbook whose value contains a forward slash. The lookup value is "Total Disc / Coup" and the lookup doesn't work with the forward slash in it.

    Here's formula:
    =VLOOKUP("Total Disc / Coup",'C:\FH276 QB WEEKLY 2017\Royalty\[report.xls]sheet1'!$A$1:$N$50,14,FALSE)

    Thanks for any assistance.
    "I wish I could do this."

  2. #2
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    79,958
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Vlookup Help

    Are you getting #N/A? If so, that means Total Disc / Coup is not available in sheet1'!$A$1:$A$50...
    Assuming too much and qualifying too much are two faces of the same problem.

  3. #3
    Board Regular
    Join Date
    Jan 2004
    Posts
    143
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Vlookup Help

    I have the range correct, Excel doesn't allow a lookup with values containing a forward slash.
    "I wish I could do this."

  4. #4
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    79,958
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Vlookup Help

    Quote Originally Posted by JLouis View Post
    I have the range correct, Excel doesn't allow a lookup with values containing a forward slash.
    I wasn't talking about the correctness of "the range". If you get #N/A, that's because the match-range. i.e. the first column of your table range, does not house the string Total Disc / Coup, not because Excel does not allow for this string a forward slash...

    Example:

    Row\Col
    A
    B
    C
    D
    E
    1
    2
    x
    100
    300
    3
    jad
    200
    4
    Total Disc / Coup
    300
    5
    vad
    400
    6
    7


    E2 houses a formula similar to yours:

    =VLOOKUP("Total Disc / Coup",Sheet1!$A$1:$B$20,2,0)
    Assuming too much and qualifying too much are two faces of the same problem.

  5. #5
    Board Regular
    Join Date
    Jan 2004
    Posts
    143
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Vlookup Help

    It's returning a "0". I have the other sheet open. I tried the formula on the same sheet and got a zero. When I take out the forward slash it works. A Space problem perhaps? I actually copied and pasted the value into the formula so the text would be exact.
    Last edited by JLouis; Aug 12th, 2017 at 05:56 PM.
    "I wish I could do this."

  6. #6
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    79,958
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Vlookup Help

    Quote Originally Posted by JLouis View Post
    It's returning a "0". I have the other sheet open. I tried the formula on the same sheet and got a zero. When I take out the forward slash it works. A Space problem perhaps?
    What should be the result?
    Assuming too much and qualifying too much are two faces of the same problem.

  7. #7
    Board Regular
    Join Date
    Jan 2004
    Posts
    143
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Vlookup Help

    $52.81

    Total Discounts $15.68
    Total Coupon $37.13
    Total Disc / Coup $52.81
    Charge Deposits
    Visa $1,006.56
    Diners / Misc
    Last edited by JLouis; Aug 12th, 2017 at 05:57 PM.
    "I wish I could do this."

  8. #8
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    79,958
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Vlookup Help

    In which column are those numbers?
    Assuming too much and qualifying too much are two faces of the same problem.

  9. #9
    Board Regular
    Join Date
    Jan 2004
    Posts
    143
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Vlookup Help

    "A20" is the text and "N20" is the value
    Last edited by JLouis; Aug 12th, 2017 at 06:04 PM.
    "I wish I could do this."

  10. #10
    Board Regular
    Join Date
    Jan 2004
    Posts
    143
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Vlookup Help

    BTW, I went to my laptop and was able to do as you say across two workbooks without problem. I am exporting the sheet off the internet in xls format. Would that affect the behavior of the worksheet?
    "I wish I could do this."

User Tag List

Tags for this Thread

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
  •  
This website uses cookies
We use cookies to store session information to facilitate remembering your login information, to allow you to save website preferences, to personalise content and ads, to provide social media features and to analyse our traffic. We also share information about your use of our site with our social media, advertising and analytics partners.
     


DMCA.com