Hi
Can I use vlookup to search for data over several workbooks?

For example look for a part number which could be stored in seperate
workbooks for different manufacturers.

If so can someone give me an example?

Many thanks.

Yes, you can.

Easiest way to do this is to open all the workbooks involved. Then setup
your VLOOKUP() formulas by choosing the different workbooks, sheets within
those workbooks and table ranges needed while they're open. Excel will build
the formulas for you. Then when you close the workbooks, Excel will update
those formulas to show the entire path to the others in your workbook with
the VLOOKUP() formulas.

If you need more help, just let us know. But basically it's just like
setting up a regular VLOOKUP() to other sheets in the same workbook except
you have the extra step of choosing a different workbook before choosing a
different sheet.

Initially a formula might look something like:
=VLOOKUP(A1,[vlookupsource1.xls]Sheet1!$A$1:$C$4,2,FALSE)
(where vlookupsource1.xls is the workbook with the table to be searched), then
when you close that workbook, the formula will automatically change to
include the full path as:
=VLOOKUP(A1,'C:\Documents and Settings\jlatham\My
Documents\[vlookupsource1.xls]Sheet1'!$A$1:$C$4,2,FALSE)

Excel automatically adjust the formulas depending on whether or not the
source workbook(s) is/are open after they've been set up initially.

"F Dee P" wrote:

Hi
Can I use vlookup to search for data over several workbooks?

For example look for a part number which could be stored in seperate
workbooks for different manufacturers.

If so can someone give me an example?

Many thanks.

Many thanks. I will try it tomorrow. Very impressed with the speed of responce.

"JLatham" wrote:

Yes, you can.

Easiest way to do this is to open all the workbooks involved. Then setup
your VLOOKUP() formulas by choosing the different workbooks, sheets within
those workbooks and table ranges needed while they're open. Excel will build
the formulas for you. Then when you close the workbooks, Excel will update
those formulas to show the entire path to the others in your workbook with
the VLOOKUP() formulas.

If you need more help, just let us know. But basically it's just like
setting up a regular VLOOKUP() to other sheets in the same workbook except
you have the extra step of choosing a different workbook before choosing a
different sheet.

Initially a formula might look something like:
=VLOOKUP(A1,[vlookupsource1.xls]Sheet1!$A$1:$C$4,2,FALSE)
(where vlookupsource1.xls is the workbook with the table to be searched), then
when you close that workbook, the formula will automatically change to
include the full path as:
=VLOOKUP(A1,'C:\Documents and Settings\jlatham\My
Documents\[vlookupsource1.xls]Sheet1'!$A$1:$C$4,2,FALSE)

Excel automatically adjust the formulas depending on whether or not the
source workbook(s) is/are open after they've been set up initially.

"F Dee P" wrote:

Hi
Can I use vlookup to search for data over several workbooks?

For example look for a part number which could be stored in seperate
workbooks for different manufacturers.

If so can someone give me an example?

Many thanks.