Vlookup referencing different workbook

[twarden]

Hopefully this is a simple question - but its one I haven't been able to solve on my own. How can a vlookup function be used to reference a 'table' of data in another workbook (workbook is also in a different root directory on our network if that matters). I don't want to simply coy the data from the 'other' workbook into the one I'm currently working on as I need the data centralized, because other workbooks refer to it, and I don't want to have to make updates/changes to the data in multiple workbooks....

If that's confusing - here's the code I'm using - currently I am getting the generic #N/A error message in the formula

=VLOOKUP(B2,'T:\ACCD\WEI\PF\[Content Ranking.xls]Summary II'!$A:$D,3,FALSE)

Any ideas on how I can get this to work?

 

[Aladin Akyurek]

Hopefully this is a simple question - but its one I haven't been able to solve on my own. How can a vlookup function be used to reference a 'table' of data in another workbook (workbook is also in a different root directory on our network if that matters). I don't want to simply coy the data from the 'other' workbook into the one I'm currently working on as I need the data centralized, because other workbooks refer to it, and I don't want to have to make updates/changes to the data in multiple workbooks....

If that's confusing - here's the code I'm using - currently I am getting the generic #N/A error message in the formula

=VLOOKUP(B2,'T:\ACCD\WEI\PF\[Content Ranking.xls]Summary II'!$A:$D,3,FALSE)

Any ideas on how I can get this to work?

The formula in itself should not be an issue. You could check whether the B2 value is really in the target book. If it is there, check the situation with:

=B2=The cell in column A in the target book

 

[twarden]

Hi Aladin,

I've tried many times to have a vlookup formula reference a table in another workbook but it's never worked - ever. So I assume that I'm doing something wrong. I've double checked, and yes, the value does exist in the workbook (for the current problem i'm experiencing... I haven't gone back to check all my previous attempts - but I'm only concerned with the current task I'm trying to complete).

Any other ideas what it might be?

 

[bryhamm]

I just tested it out and had no problem. Is the data in cell B2 in the same format as column A in the lookup file (i.e. one is not a number and the other a text)?

 

[Matty]

One thing to check is that there are no excess spaces in either B2 or Column A in your table array, as this will prevent the lookup from working (unless both have excess spaces in the same place!).

If you do have excess spaces, you can use TRIM to get rid of them.

Matty

 

[Aladin Akyurek]

I just tested it out and had no problem. Is the data in cell B2 in the same format as column A in the lookup file (i.e. one is not a number and the other a text)?

Did you run the equality test I suggested? If so, what is the result?