VLOOKUP PROBS!!!
Power Query Course in Spanish
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 3 123 LastLast
Results 1 to 10 of 22

Thread: VLOOKUP PROBS!!!

  1. #1
    Guest

    Default

     
    HI, I do am a novice in excel and can't figure out how to return a zero value using vlookup IF the info isn't there. I import a .txt doc in excel to calculate the remaining work orders for product that month. If there is no work order for that item I just get #VALUE! when I'd like to have a zero. Here is my formula where REM.TXT is the doc refered to.

    =VLOOKUP(B10,REM.TXT!$B$1:$J$700,5)-VLOOKUP(B10,REM.TXT!$B$1:N$700,7)

    Please let me know if u need more info. Thanks a lot!!


  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Denver, CO
    Posts
    1,743
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-03-05 13:11, Anonymous wrote:
    HI, I do am a novice in excel and can't figure out how to return a zero value using vlookup IF the info isn't there. I import a .txt doc in excel to calculate the remaining work orders for product that month. If there is no work order for that item I just get #VALUE! when I'd like to have a zero. Here is my formula where REM.TXT is the doc refered to.

    =VLOOKUP(B10,REM.TXT!$B$1:$J$700,5)-VLOOKUP(B10,REM.TXT!$B$1:N$700,7)

    Please let me know if u need more info. Thanks a lot!!

    Do you get a value error or an n/a error?

  3. #3
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Your VLOOKUPs are returning non-numeric data to which you are applying the minus operator. That's why you're getting the #VALUE! error. I noticed that you didn't supply the 4th (optional) argument to your VLOOKUPs so they're not performing an exact match. Is that what you really want? Also, you're performing 2 VLOOKUPs when only one is necessary...

    =SUM(VLOOKUP(B10,REM.TXT!$B$1:$J$700,{5,7},0)*{1,-1})

    [ This Message was edited by: Mark W. on 2002-03-05 13:17 ]

    [ This Message was edited by: Mark W. on 2002-03-05 13:20 ]

  4. #4
    New Member
    Join Date
    Mar 2002
    Posts
    10
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I tried that formula with the same result. It is giving me zeros when I have data, and the same #VALUE! when there is none.

    What it is doing is subtracting the original number projected for the beginning of the month from what has been built, giving me the balance as to what will be built. I am getting the #VALUE! error all the time.

  5. #5
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Of course, my formula gave the same #VALUE! error. The problem isn't the formula... (I believe) its the contents of the 5th and/or 7th column of your lookup table. What's in those columns?

    [ This Message was edited by: Mark W. on 2002-03-05 13:50 ]

  6. #6
    Guest

    Default

    Mark;

    Help me understand the function of the {bracketed} items.

    thanks

    Scott


    On 2002-03-05 13:16, Mark W. wrote:
    Your VLOOKUPs are returning non-numeric data to which you are applying the minus operator. That's why you're getting the #VALUE! error. I noticed that you didn't supply the 4th (optional) argument to your VLOOKUPs so they're not performing an exact match. Is that what you really want? Also, you're performing 2 VLOOKUPs when only one is necessary...

    =SUM(VLOOKUP(B10,REM.TXT!$B$1:$J$700,{5,7},0)*{1,-1})

    [ This Message was edited by: Mark W. on 2002-03-05 13:17 ]

    [ This Message was edited by: Mark W. on 2002-03-05 13:20 ]

  7. #7
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-03-05 13:53, Anonymous wrote:
    Mark;

    Help me understand the function of the {bracketed} items.

    thanks

    Scott
    Scott, the use of an array constant as the 3rd VLOOKUP (Col_index_num) argument means I want to return values from those columns -- all at once! I should have been more explicit about the nature of this formula because once you use an array constant as the 3rd argument it should be entered as an array formula...

    {=SUM(VLOOKUP(B10,REM.TXT!$B$1:$J$700,{5,7},0)*{1,-1})}

    ...and, array formulas must be entered using the Control+Shift+Enter key combination. The outermost braces, { }, are not entered by you -- they're supplied by Excel in recognition of a properly entered array formula.

    So... having said that... this VLOOKUP should then return a 2-element, horizontal array which I multiply by {1,-1} and then sum. {=SUM({1,2}*{1,-1})} is the same as saying, =SUM(1,-2) or =1-2, but allows me to do it more economically -- with 1 VLOOKUP. Depending on the length of the lookup table and the type of lookup (exact or approximate) VLOOKUPs can be quite "expensive"!

    [ This Message was edited by: Mark W. on 2002-03-05 14:29 ]

  8. #8
    New Member
    Join Date
    Mar 2002
    Posts
    10
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Sorry if I'm being redundant, but what I'm trying to communicate is that sometimes the value that vlookup is searching for (b10) is not in the column because it's value is zero. I'm trying to get it to relay that zero onto the original sheet but only #VALUE! comes up. Hope that helps.

  9. #9
    New Member
    Join Date
    Mar 2002
    Posts
    10
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    To clarify further, some months we don't make work orders for a specific product because we don't need it. I still want it to post as 0 on my sheet, but the referred part and quantity don't exist. I'm just trying to get it to "default" to zero if nothing is there because any other calculations based on that cell come up with, yes, #VALUE!..........

  10. #10
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    81,807
    Post Thanks / Like
    Mentioned
    12 Post(s)
    Tagged
    1 Thread(s)

    Default

      
    On 2002-03-05 13:11, Anonymous wrote:
    HI, I do am a novice in excel and can't figure out how to return a zero value using vlookup IF the info isn't there. I import a .txt doc in excel to calculate the remaining work orders for product that month. If there is no work order for that item I just get #VALUE! when I'd like to have a zero. Here is my formula where REM.TXT is the doc refered to.

    =VLOOKUP(B10,REM.TXT!$B$1:$J$700,5)-VLOOKUP(B10,REM.TXT!$B$1:N$700,7)

    Please let me know if u need more info. Thanks a lot!!

    Try the following, specifically for a value in B10 that gives you a #VALUE error:

    in some cell enter and note the result:

    =VLOOKUP(B10,REM.TXT!$B$1:$J$700,5)

    in another cell enter and note result:

    VLOOKUP(B10,REM.TXT!$B$1:N$700,7)

    Would you post the noted results in the follow up?


User Tag List

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