VLOOKUP to an external workbook

[trillicomm]

VLOOOKUP work fine if everything is in the same workbook. But I can't make a VLOOKUP to look into another workbook. I need your help to point out what the steps are to make VLOOKUP work among multiple workbooks. Please allow me to explain if this does not clarify the problem.

Waiting to hear from anyone!

T.

 

[Aladin Akyurek]

An example:

=VLOOKUP("a",'C:\Aladin\Misc\TestDir\[2001 TestWB.xls]Data'!$B$2:$E$4,3,0)

 

[Smitty]

You can do it if the other workbook is open or closed, but it's easier if it's open.

Start your Vlookup, then ALT+W to locate & activate your other workbook, select your Source Table range & Column count, then enter.

You should get something like this:

=VLOOKUP(A1,'[The Other Workbook.xls]Sheet Name'!$C$12,1,FALSE)

Note that when doing this, Excel will automatically make the Source Table range absolute.

HTH,

Smitty

 

[trillicomm]

An example:

=VLOOKUP("a",'C:\Aladin\Misc\TestDir\[2001 TestWB.xls]Data'!$B$2:$E$4,3,0)

Thanks! That works assuming I have a valid table array.

It does not work for me because my table array does not have the valid cell format somehow. Therefore VLOOKUP returns with a N/A. I found out that I have to hit F2 and Enter to each cell in the table array to "validate" in order for the VLOOKUP to return the value. But I have a 27000 rows in the table array and the table is a query from MS Access database. I think whatever Access transfers Excel causes the cell format unrecognized by VLOOKUP. The manual solution is to hit F2 and Enter like I said above or create a macro to validate the enter 27000 rows. I even think when Access transfers UNICODE during the query and that may be the root cause. Any idea why F2 and Enter makes VLOOKUP to work?

 

[Smitty]

It's due to Format changes. I have the same problem when I pull data out of our AS400 system.

Format your column the way that you want it, insert a column next to it then Data-->Text to Columns & target range the new column. Your column should now have been effectively "F2'd".

Hope that helps,

Smitty

 

[Cam]

If you don't want to take the time to format ur column, using pennysaver's example with a slight edit, should pull in the result, without having to format.

=VLOOKUP(text(A1,0),'[The Other Workbook.xls]Sheet Name'!$C$12,1,FALSE)

 

[Smitty]

Nice tip Cam, I'll keep that one handy.

 

[Aladin Akyurek]

If you don't want to take the time to format ur column, using pennysaver's example with a slight edit, should pull in the result, without having to format.

=VLOOKUP(text(A1,0),'[The Other Workbook.xls]Sheet Name'!$C$12,1,FALSE)

Shorter, might even be faster...

=VLOOKUP(A1&"",...)

should be last to resort to though.

 

[Cam]

Just when I thought I had it nailed down, the guru of guru's steps in to show me the light. Nice tip Aladin

 

[trillicomm]

You guys are good, esp. Aladin!

Thanks a bunch ray: !