Looking up information in 2 different workbooks

[Guest]

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

 

[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!

 

[Guest]

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

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!

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

 

[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!