VLOOKUP PROBS!!! - Page 2
VLOOKUP PROBS!!!
Thanks Thanks:  0
Likes Likes:  0
Page 2 of 3 FirstFirst 123 LastLast
Results 11 to 20 of 22

Thread: VLOOKUP PROBS!!!

  1. #11
    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 14:34, cnickerson wrote:
    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.
    This is not the cause of your #VALUE! error. Take a look at the Help Index topics for "What does the error #VALUE! mean?" and "VLOOKUP worksheet function". VLOOKUP produces #N/A when it can't find it's lookup value -- not #VALUE!.

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

    Default

    #VALUE! for both functions. Column 5 is the number of items to be built, and column 7 is the number of items that HAVE BEEN built. My prob. is when there is nothing for VLOOKUP to find, I'd like it to report zero.

    Thanks again!!!

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

    Default

    The B10 that my formula refers to is a SPED.PBKA prod. code. The vlookup then goes to REM.TXT, finds that code and looks at columns 5&7 to determine the amount. Is my problem that is not finding the SPED.PBKA and stops there?? If so, I again need it to report zero if that product code isn't on REM.TXT

  4. #14
    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

    It does not stop there!!! You haven't supplied a 4th argument to your VLOOKUPs so it assumes that your lookup table is sorted and it "grabs" the next closest entry. Do you understand this distinction of how VLOOKUPs work?

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

    Default

    Yes, I did read about that in the help file, but wasn't sure how it was applied. My problem is I didn't set this up in the first place and have a limited knowledge of Excel. Is VLOOKUP the ideal function for what I'm trying to accomplish?

    Thanks man.

  6. #16
    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 14:55, cnickerson wrote:
    #VALUE! for both functions. Column 5 is the number of items to be built, and column 7 is the number of items that HAVE BEEN built. My prob. is when there is nothing for VLOOKUP to find, I'd like it to report zero.

    Thanks again!!!
    I think I understand what you want. It's of importance that we track down why you get #VALUE! error before giving you the desired zeroes in case of failure.

    I asked for the info to see whether one of the VLOOKUPs returned a space that would lead to a #VALUE error.

    If you get correct results for some lookup values and for others not, there must be something wrong with REM.TXT.


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

    Default

    If there are values in REM.TXT then the VLOOKUP reports the numbers correctly, however if the product code it is looking for is not on the report...........#VALUE! But otherwise it works fine.

  8. #18
    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 15:13, cnickerson wrote:
    Yes, I did read about that in the help file, but wasn't sure how it was applied. My problem is I didn't set this up in the first place and have a limited knowledge of Excel. Is VLOOKUP the ideal function for what I'm trying to accomplish?

    Thanks man.
    First, let's go back and use my suggested array formula...

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

    ...this formula 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.

    Please reconfirm that this is producing a #VALUE! error. I trust that REM.TXT is a worksheet in the same workbook as your lookup value. Is that the case?


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

  9. #19
    Guest

    Default

    Got it. Now THAT is cool.

    On 2002-03-05 14:23, Mark W. wrote:
    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 ]

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

    Default

      
    Yes Mark, I did enter it with Ctrl,Shift, and Enter, and came up with the same #VALUE! I know that because I tried entering it normally without any results. The REM.TXT is a file I open along with the regular worksheet (amongst other files). I don't think it's in the same workbook. When I open REM.TXT I have to set the columns etc, so everything lines up. But again, if there isn't a product code for VLOOKUP to find it returns #VALUE! I know I'm sayin the same thing over and over, but that's the case.

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