Incorrect VLOOKUP Results

[Andy Pilkington]

Please can anyone help?

I have a spreadsheet on which I store data which is accessed by and linked to other spreadsheets using the VLOOUP function.

The file and its links have worked entirely satisfactorily for over ten years until a few weeks ago but now the results of the VLOOKUP formula are incorrect.

Has anyone experienced this before and is there a simple fix I am missing?

Thank you in advance.

 

[Marcílio_Lobão]

Andy Pilkington, Good morning.

Without seeing the files and the link, what we have left is to ask some questions that you may have probably asked.

But it’s always good to reinforce.

1) Is the second spreadsheet that is accessed with the same name and in the same place?

2) Is the vlookup table the same size as it should be?

3) Do you still have permission to access the second spreadsheet as you always have?

4) Have you checked if the link is broken?

I hope to be helping you with these questions.

 

[Andy Pilkington]

Andy Pilkington, Good morning.

Without seeing the files and the link, what we have left is to ask some questions that you may have probably asked.

But it’s always good to reinforce.

1) Is the second spreadsheet that is accessed with the same name and in the same place?

2) Is the vlookup table the same size as it should be?

3) Do you still have permission to access the second spreadsheet as you always have?

4) Have you checked if the link is broken?

I hope to be helping you with these questions.

Hi Marcílio

I'll answer the questions in the same order you have asked ...
1 No. The linked files are in a separate directory and all have different names.
2 The Vlookup table has remained a consistent size since creating it many years ago.
3 Yes. The file in question is password protected but the password has remained the same since its creation.
4 The link is intact.

Does this help with your thoughts?

Thanks

 

[Marcílio_Lobão]

Andy Pilkington,

Unfortunately that was all I could think of at the moment trying to help you.

Surely someone will appear to help you effectively.

 

[MARK858]

Can you post your VLOOKUP formula please.

 

[Andy Pilkington]

Can you post your VLOOKUP formula please.

=VLOOKUP(L10,'C:\AP 2006\Excell\Management.xlsm'!InvDat,2)

 

[MARK858]

Ok, it's not what I thought it might be (I was looking to see if you were doing an exact or approximate match).
I think that you will probably need to post your Lookup data to go any further.

 

[Andy Pilkington]

Andy Pilkington,

Unfortunately that was all I could think of at the moment trying to help you.

Surely someone will appear to help you effectively.

Thank you Marcílio

 

[RoryA]

=VLOOKUP(L10,'C:\AP 2006\Excell\Management.xlsm'!InvDat,2)

That formula requires the data in the lookup table to be sorted in ascending order on the first column. If it isn't, then you would get some incorrect results in all likelihood.

 

[Andy Pilkington]

Column 1 is a numerical list starting at 1 and increasing by 1 each row. The data range is a named range [InvData - $A$6:$L$3002] but the peculiar thing is that when I use the same data range without actually using the name I get the same error result, until I manually reduce the range. I have found that when the range is reduced to $A$6:$L$967 the lookup works but if I extend the range to $A$6:$L$968 and higher then it gives an erroneous result.