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

    vlookup woes (xp & 2003)

    I am looking up (VLOOKUP) a downloaded value. Attached workbook has two worksheets. One worksheet is JE the other is DNLD. The DNLD was the worksheet where the original values reside. Using column A on both worksheets JE and DNLD as the lookup value, I found the values from DNLD utilizing the VLOOKUP on the JE worksheet.

    Since the total of the LOOKUP column on JE did not agree with the total on the LOOKUP column on the DNLD worksheet, I performed the VLOOKUP on the DNLD to find which items on the JE worksheet were not recorded.

    Some of the formula worked, however the other cells just return a "0".

    What is going on with the verified VLOOKUP value on the DNLD worksheet?

    Why isn't it working for all the items?

    Thanks

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

    Re: vlookup woes (xp & 2003)

    You need to apply TRIM to the lookup range too, since the values in column A on the JE sheet may contain trailing spaces as well. This requires the formula to become an array formula, i.e. confirm with Ctrl+Shift+Enter instead of just Enter. You'll see brackets { } around the formula in the formula bar - don't type them yourself!

    See attached version.

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

    Re: vlookup woes (xp & 2003)

    Thank you Hans.

    what does the value do that the "0" in the formula did not do?

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

    Re: vlookup woes (xp & 2003)

    Sorry, I don't understand your question.

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

    Re: vlookup woes (xp & 2003)

    Hans,

    In my original formula, I had a ' "0" " to be placed in the cell if there was no lookup value. Your formula recorded a " ,0,value ". My question is what does the " ,0, value " do? Replace the cell with a zero like the " "0" " I used.

    Thanks

    Martin

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: vlookup woes (xp & 2003)

    i am a little <img src=/S/confused.gif border=0 alt=confused width=15 height=20>. The formula in Hans' workbook (eg in F2):
    =IF(ISNA(VLOOKUP(TRIM(A2),TRIM(JE!$A$2:$B$170),1,F ALSE)),"0",VLOOKUP(TRIM(A2),TRIM(JE!$A$2:$B$170),1 ,FALSE))
    puts a zero ("0") if the item is not found.

    What formula are you using?

    Steve

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

    Re: vlookup woes (xp & 2003)

    Steve,

    Hans' formula is on w/s tab "J/E" column B. The other worksheet was my formula.

    Martin

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

    Re: vlookup woes (xp & 2003)

    The only difference is that "0" results in a text value, while 0 results in a number value. If you look closely, you'll see that the 0 results in column B on the JE sheet are displayed as - because the number format displays zero values that way. The 0 results in column F on the DNLD sheet are left-aligned, and not displayed as - because they are not seen as numbers.
    But this is not essential - you can easily change either formula to resemble the other.

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

    Re: vlookup woes (xp & 2003)

    Cool.

    Thank you so much

    Martin

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

    Re: vlookup woes (xp & 2003)

    I have been trying to accomplish the vlookup formula, as described in the preceding posts, between the two worksheets 1>( PAYROLL JE_W_CASH BENEFITS) and 2>( Downloaded sept 06 ps exp) in the workbook(MAW PAYROLL JE FOR TESTING LOOKUP FORMULA)

    On the first lookup using worksheet 1, It appears its working in column R. However, the results in column S on some occassions omes up with some #N/A when it should be a zero (0).

    On the Second worksheet the results in columnG, are not working, in fact, the formula could not even be executed.

    What is it that I am not doing correctly or is it the formula?

    Martin

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

    Re: vlookup woes (xp & 2003)

    1) Replace VLLOKUP with VLOOKUP in the formulas.
    2) Replace FALESE with FALSE in the formulas.

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

    Re: vlookup woes (xp & 2003)

    Hans,

    The syntax on the first worksheet was corrected and it works great. The second worksheet "Downloaded Sept 06 PS Exp" still has the same problems as noted in my prior posting.

    Thanks

    Martin

  13. #13
    2 Star Lounger
    Join Date
    Mar 2004
    Location
    Cincinnati, Ohio, USA
    Posts
    102
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: vlookup woes (xp & 2003)

    Regarding column G in the "Downloaded Sep 06 PS Expense" sheet:

    You are applying a Trim function to the lookup table (Payroll JE_W_Cash Benefits!$N$8:$O$177). The only column that needs to be trimmed is column N of that block. Typically if I'm using a column or lookup purposes, I'll make sure that all leading spaces have been trimmed. It cuts down on a lot of overhead in my formulas.

    You are also applying a Value function to a non-numeric result.

    Your original formula was:
    =IF(ISNA(VLOOKUP(TRIM(B3),TRIM('PAYROLL JE_ W_ CASH BENEFITS'!$N$8:$O$177,1,FALSE))),0,VALUE(VLOOKUP(T RIM(B3),TRIM('PAYROLL JE_ W_ CASH BENEFITS'!$N$8:$O$177,1,FALSE))))

    I reduced it to:
    =IF(ISNA(VLOOKUP(TRIM(B3),'PAYROLL JE_ W_ CASH BENEFITS'!$N$8:$O$177,1,FALSE)),0,VLOOKUP(TRIM(B3) ,'PAYROLL JE_ W_ CASH BENEFITS'!$N$8:$O$177,1,FALSE))

    You are returning the first column from the lookup table? If that was really your intention. you could get by with:

    =IF(ISNA(VLOOKUP(TRIM(B3),'PAYROLL JE_ W_ CASH BENEFITS'!$N$8:$O$177,1,FALSE)),0,B3)

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

    Re: vlookup woes (xp & 2003)

    It would help if you did some minimal checking of your formulas, such as looking for spelling errors and missing parentheses.
    In the formula in column G of the Downloaded ... sheet, you forgot the closing parentheses after TRIM('PAYROLL JE_ W_ CASH BENEFITS'!$N$8:$O$177
    Also, the column argument in the VLOOKUP function is 1, i.e. you return the lookup value. Since this is a string such as 1011WA, you cannot compute the VALUE.
    I don't know which column you want to return, but the range N8:O177 has only two columns, both of which are text.

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

    Re: vlookup woes (xp & 2003)

    Thanks Hans.

    I reviewed many times but I overlooked those corrections. I will more aware of it. Thank you again.

    Martin

Posting Permissions

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