Vlookup referencing different workbook

twarden

Board Regular
Joined
Jun 29, 2006
Messages
64
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

Code:
=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?
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,210
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

Code:
=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

Board Regular
Joined
Jun 29, 2006
Messages
64
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

Board Regular
Joined
Jun 3, 2004
Messages
115
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

Well-known Member
Joined
Feb 17, 2007
Messages
3,717
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,210
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?
 

Forum statistics

Threads
1,181,102
Messages
5,928,069
Members
436,586
Latest member
latintxn

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top