Looking up information in 2 different workbooks

C

craighurst

hi,

Im not sure how to use a formula for this problem.

On worksheet 1 (daily log) i wish to input a contract number that is asigned
to a reg number which will be inputted.

i.e.

reg order no
yt02hjg ?

the order number is located in either workbook 2 (cars) or workbook 3 (vans)
so i want a formaula that will search both workbooks for the order number and
input it.

hope that makes sence.

cheers
 
D

Domenic

Assuming that both workbooks contain your lookup tables in A1:B100,
try...

=IF(ISNA(VLOOKUP(C1,'[Workbook2.xls]Sheet1'!$A$1:$B$100,2,0)),VLOOKUP(C1,
'[Workbook3.xls]Sheet1'!$A$1:$B$100,2,0),VLOOKUP(C1,'[Workbook2.xls]Sheet
1'!$A$1:$C$100,2,0))

....where C1 contains your lookup value.

If you have several workbooks containing your lookup tables, try...

=VLOOKUP(C1,INDIRECT("'"&INDEX(E1:E5,MATCH(TRUE,COUNTIF(INDIRECT("'"&E1:E
5&"'!A1:A100"),C1)>0,0))&"'!A1:B100"),2,0)

....confirmed with CONTROL+SHIFT+ENTER, and where C1 contains your lookup
value, and E1:E5 contains your list of workbook names.

Note that with the latter formula, the workbooks containing your lookup
tables need to be opened.

Hope this helps!
 
C

craighurst

cant seem to get that to work, not sure it i gave good information.

workbook 2 is for cars and 3 is for vans and are set out like this:

column a b c
reg Date contract no

yt02ecz 02 feb 4566

i have column A info inputted on workbook 1 and need to have conrtact no
from column C inputted automatically in the adjacent cell, the reg looked up
will be on either workbook 2 or 3. the normal look up formula will not work
as it only searches 1 workbook and not 2.

cheers for the help

Domenic said:
Assuming that both workbooks contain your lookup tables in A1:B100,
try...

=IF(ISNA(VLOOKUP(C1,'[Workbook2.xls]Sheet1'!$A$1:$B$100,2,0)),VLOOKUP(C1,
'[Workbook3.xls]Sheet1'!$A$1:$B$100,2,0),VLOOKUP(C1,'[Workbook2.xls]Sheet
1'!$A$1:$C$100,2,0))

....where C1 contains your lookup value.

If you have several workbooks containing your lookup tables, try...

=VLOOKUP(C1,INDIRECT("'"&INDEX(E1:E5,MATCH(TRUE,COUNTIF(INDIRECT("'"&E1:E
5&"'!A1:A100"),C1)>0,0))&"'!A1:B100"),2,0)

....confirmed with CONTROL+SHIFT+ENTER, and where C1 contains your lookup
value, and E1:E5 contains your list of workbook names.

Note that with the latter formula, the workbooks containing your lookup
tables need to be opened.

Hope this helps!

craighurst said:
hi,

Im not sure how to use a formula for this problem.

On worksheet 1 (daily log) i wish to input a contract number that is asigned
to a reg number which will be inputted.

i.e.

reg order no
yt02hjg ?

the order number is located in either workbook 2 (cars) or workbook 3 (vans)
so i want a formaula that will search both workbooks for the order number and
input it.

hope that makes sence.

cheers
Click to expand...
Click to expand...
 
D

Domenic

Try...

=IF(ISNA(VLOOKUP(A1,'[Workbook2.xls]Sheet1'!$A$2:$C$100,3,0)),VLOOKUP(A1,
'[Workbook3.xls]Sheet1'!$A$2:$C$100,3,0),VLOOKUP(A1,'[Workbook2.xls]Sheet
1'!$A$2:$C$100,3,0))

Replace the workbook names (Workbook2.xls and Workbook3.xls) and sheet
names with your actual names. Also, adjust the range ($A$2:$C$100)
accordingly.

Hope this helps!
 
Top