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
    80,731
    Post Thanks / Like
    Mentioned
    5 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
    80,731
    Post Thanks / Like
    Mentioned
    5 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
    80,731
    Post Thanks / Like
    Mentioned
    5 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
    80,731
    Post Thanks / Like
    Mentioned
    5 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
  •  


DMCA.com