Vlookup not working properly

**JESSIER4025** · 01-24-2014, 03:19 PM

I am having some trouble with Vlookup.
Example:
Workbook A- is full of vlookup formulas that worked perfectly.

I copied Workbook A and renamed it Workbook B.

Workbook A and B vlookups should be looking at another source Workbook C for matches.

I used the find and replace function to make changes to workbook b that needed to be made and my vlookups still worked fine. The only time i run into a snag with the vlookup result is if i manually type my data into a vlookup reference cell or copy and paste text into it.
If i copy and paste the text from workbook c the vlookups work.
Again manually typing the same text with same spacing doesnt work or if i copy and paste from somewhere else other than workbook c it doesnt work.
Any suggestions why it would not work with this scenario?
Thanks

**xladept** · 01-24-2014, 03:23 PM

Attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are demonstrated, mock them up manually if needed. Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

**JESSIER4025** · 01-24-2014, 03:40 PM

When i try to create a sample workbook it works fine. That is what is puzzling.
I will continue to try to replicate the issue and upload once i can get it to repeat.

The copied file is the only problem "Workbook B"
For example cell f5 has to look at b5 as a reference.
Formula in f5 =IF(ISBLANK(B:B),"",VLOOKUP(B:B,'C:\Documents and Settings\rstevens1\Desktop\Window Takeoffs\Plygem Window Pricing\Window Pricing Templates\[Window Pricing Sheet.xlsx]Sheet1'!$A:$C,2,FALSE))

So whatever is in b5 it looks at "Workbook C" and returns the result of the vlookup for column 2 into cell F5.
If i manually type the exact same thing from workbook c into b5 i get #n/a. if i copy the text from workbook c it returns a value.

This only occurs when i copy a workbook and rename it.

Again, if i can replicate the issue i will upload sample files.

thanks

**JESSIER4025** · 01-24-2014, 08:44 PM

I figured out what my issue was. I had trailing spaces in the workbook that contained the data where Vlookup was searching.
I had someone help me with vba code which confirmed this. It fixed one sheet but not all of the sheets in the workbook. What part of this code needs to be changed to have it run through the entire workbook?

Please Login or Register  to view this content.

**xladept** · 01-24-2014, 08:59 PM

Hi Jessier,

I'm not really clear on what you're doing, but this should do it to every sheet in your book:

Please Login or Register  to view this content.

Edit* Oh Bummer, I had your code still on my clipboard - I'll post the correction tomorrow when I have my other machine up - Sorry!

**FDibbins** · 01-24-2014, 09:06 PM

For a start off, you need to adjust some of the ranges in your formula...
=IF(ISBLANK(B:B),"",VLOOKUP(B:B,'C:\Documents and Settings\rstevens1\Desktop\Window Takeoffs\Plygem Window Pricing\Window Pricing Templates\[Window Pricing Sheet.xlsx]Sheet1'!$A:$C,2,FALSE))
to something like thtis...
=IF(IB2="","",VLOOKUP(trim(B2),'C:\Documents and Settings\rstevens1\Desktop\Window Takeoffs\Plygem Window Pricing\Window Pricing Templates\[Window Pricing Sheet.xlsx]Sheet1'!$A:$C,2,FALSE))

Also, it will probably speed things up a bit if you restricted the ranges to jusat what you need, instead of using entire columns

**AlKey** · 01-24-2014, 09:08 PM

I think here is your problem

=IF(ISBLANK(B:B),"",VLOOKUP(B:B,'C:\Documents and Settings\rstevens1\Desktop\Window Takeoffs\Plygem Window Pricing\Window Pricing Templates\[Window Pricing Sheet.xlsx]Sheet1'!$A:$C,2,FALSE))

1. It is your range. It has to be B:C not A:C

2. you can't lookup B:B it has to be cell reference.

**FDibbins** · 01-24-2014, 09:10 PM

Good catch, AlKey, I should have spotted that too

**AlKey** · 01-24-2014, 09:15 PM

Originally Posted by FDibbins

Good catch, AlKey, I should have spotted that too

Well, four of eyes better than two

**FDibbins** · 01-24-2014, 09:18 PM

maybe so, but I already have 4 (I wear glasses lol)

**AlKey** · 01-24-2014, 09:21 PM

Originally Posted by FDibbins

maybe so, but I already have 4 (I wear glasses lol)

then I miscalculated. It should've been 8 then. (I were glasses too

BTW, thanks for Reps

**FDibbins** · 01-24-2014, 09:27 PM

well deserved

**xladept** · 01-24-2014, 09:46 PM

Hey,

I wear glasses too - ever since I turned 45 (30years)!

**FDibbins** · 01-24-2014, 09:54 PM

darn kids!! I started at 16 (I think), 40 years ago

**xladept** · 01-25-2014, 04:16 PM

Here's what I thought I was posting at #5:

Please Login or Register  to view this content.

Vlookup not working properly

LinkBack

Thread Tools

Rate This Thread

Display