vlookup across multiple files

[Ben]

Can anyone tell me the vlookup formula structure for the following scenerio:

File A, Sheet 1, A1:E1029 (source)

File B (destination)

I can't for the life of me get the formula to work to pull data from the
source file.

or

Is there an easier way to pull the data using for instance a customer # as
the reference #?

Thanks!

 

[Sheeloo]

If you enter this in B2 of Sheet2
=VLOOKUP(A1,Sheet1!A1:E1029,2,False)
then VLOOKUP will look for A1 of Sheet2 in Col A of Sheet1 and if found get
the value in Col B for that row and show that value in B2...

If you change 2 to 3 you will get the corresponding value from Col C of
Sheet1, and so on...

Hope this is clear..

You can copy this down to search for A2, A3,...

Do lookup VLOOKUP
in Excel HELP
especially the meaning of TRUE/False as the 4th parameter

 

[Bernard Liengme]

In Sheet1 of Book1.xls in column A I have a list of fruits: apple, pear,
plum....
In column B, I have some numbers
This formula
=VLOOKUP(G1,'C:\Documents and Settings\Owner\My
Documents\[Book2.xls]Sheet1'!$A$1:$B$12,2,FALSE)
returns the correct number when used in Book1 with a fruit name in G1

It is better to make the formula with both files open. Then you can use the
'pointing' method to get the correct syntax. So I typed =VLOOKUP(G1, then I
move to Book2 and selected the range A1:B12, then I typed the rest of the
formula: ,2,FALSE)

The formula will show as
=VLOOKUP(G1,[Book2.xls]Sheet1!$A$1:$B$3,2,FALSE)
when Book2 is open.
best wishes

 

[JLatham]

I believe they are working across 2 .xls files, so they'd need to include the
source file's name along with the sheet name in that workbook in order to get
the proper reference for the lookup table.

=VLOOKUP(A1,[FileA]Sheet1!$A$1:$B$6,2,FALSE)

note the use of the other file's name within the brackets.

Ben - the easiest way to set it up initially is to create the formula with
both workbooks open. Just treat them both like on big one, working to build
the formula by selecting the appropriate workbook/worksheet/ranges as you
build the formula. When you close the source workbook (File A), the formulas
will automatically update to show the full path to the file. And if you open
both workbooks again later, then Excel automatically shortens it back to just
the other filename.