vlookup returning 0 when there is a figure in cell

[Craig_Moore]

Hi All

i have started having issues with the results returning a 0 when using the vlookup, the calculation i am using is =VLOOKUP(M8,'\\Duerrsfile\ss\PNB Production Plans\[PNBRAWmatApr14.xls]Main'!$A$1:$IV$65536,32,FALSE) which i have had no issues with for a few years but i have come in to work today with the results returning a 0 when it should return 180

the optimum rate (Column Q) looks up the variety (column M) on a planning sheet which gives my team the run rate which they are to set at but this has stopped pulling through, if i open the sheet where the data is stored the figures come across

any help would be appreciated

thanks
Craig

 

[arthurbr]

VLOOKUP will not work on closed books
But you can use SUMPRODUCT instead, which does like

=sumproduct(--('\\Duerrsfile\ss\PNB Production Plans\[PNBRAWmatApr14.xls]Main'!$A$1:$A$65536=M8)*(AF1:AF65536))

Adapt ranges as required.
BTW using entire columns as references is bad practice IMO

 

[Craig_Moore]

VLOOKUP will not work on closed books
But you can use SUMPRODUCT instead, which does like

=sumproduct(--('\\Duerrsfile\ss\PNB Production Plans\[PNBRAWmatApr14.xls]Main'!$A$1:$A$65536=M8)*(AF1:AF65536))

Adapt ranges as required.
BTW using entire columns as references is bad practice IMO

Hi Arthurbr

Thanks for the reply,

vlookup dose work when the source book has been closed it has worked for the last 6 years on the workbooks i am using, the only time i have found that it doesn't work is when linking in to a workbook on a networked drive whish is different on each machine that is why i due the direct server address in the formula so it doesn't matter which computer in the business the workbook is opened in it will always find the workbook for the data required

I have never used sumprduct before but will give it a go and see if i get better results

the reason for using the full columns is we have a huge product range where the planning team add products constantly to the source workbook and never delete the old products and Ido not have the time to keep increasing the table every time a product is added

Thanks Again

Craig

 

[arthurbr]

vlookup dose work when the source book has been closed

I wasn't aware if that. thanks for the info
Then there is a good chance my suggestion won't help either

 

[Craig_Moore]

I wasn't aware if that. thanks for the info
Then there is a good chance my suggestion won't help either

it looks like one of the planners have changed the formatting of the column that is looked up since i have changed in back to number all seems to be working now

thnaks again for the help

Craig