Results 1 to 15 of 15
Thread: vlookup woes (xp & 2003)

20060930, 14:45 #1
 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

20060930, 15:15 #2
 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.

20061004, 01:14 #3
 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?

20061004, 05:30 #4
 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.

20061004, 19:13 #5
 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

20061004, 19:22 #6
 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

20061004, 19:57 #7
 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

20061004, 20:27 #8
 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 leftaligned, 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.

20061005, 02:08 #9
 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

20061011, 16:48 #10
 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

20061011, 17:11 #11
 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.

20061011, 18:43 #12
 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

20061011, 19:28 #13
 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 nonnumeric 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)

20061011, 19:39 #14
 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.

20061012, 16:18 #15
 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