# Thread: vlookup woes (xp & 2003)

1. ## 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. ## 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. ## Re: vlookup woes (xp & 2003)

Thank you Hans.

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

4. ## Re: vlookup woes (xp & 2003)

Sorry, I don't understand your question.

5. ## 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. ## 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))

What formula are you using?

Steve

7. ## 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. ## 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. ## Re: vlookup woes (xp & 2003)

Cool.

Thank you so much

Martin

10. ## 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. ## Re: vlookup woes (xp & 2003)

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

12. ## 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. ## Re: vlookup woes (xp & 2003)

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.

=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. ## 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. ## 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
•