Lookup values in 2 different workbooks.

Have a look here:

http://www.contextures.com/xlFunctions05.html#RefWkbk

note: that you will need to ensure the reference workbook is always open for this to work.

If you need it closed, there exists an addin from here: http://download.cnet.com/Morefunc/30...-10423159.html that allows you to use the INDIRECT.EXT function to access closed workbooks.

Its a big project not just a simple fix

what have you put to geather so far?

Hi Mostapha
Looking at NBVC's advice have you downloaded the add-in?
once you have the add-in you could the use if function to lookup values values on each sheet untill it find a matching value

Dude
Yep you can use =If ( not iserror (lookup sheet1),ture=lookup sheet1 ,if (not error (lookup sheet2),lookup sheet2,if (not error (lookup sheet3),lookup sheet3
for all your sheets

Mostapha
you could use match to find the value then use lookup

=IF( ISNUMBER(MATCH(B2,'[Workbook 1.xls]Earring 1'!$A:$A,0))," the lookup",IF(ISNUMBER(MATCH(B2,'[Workbook 1.xls]Necklace 2'!$A:$A,0)),"the lookup ","This code not found"))

Is there any way you can include a column in Workbook2 which identifies which worksheet it is in within Workbook1... example..is it a necklace code, earring code, cufflink code, etc...

Then you can use the INDIRECT.EXT function I mentioned at the start to link to the specific sheet and find the barcode and price.

or even if you can create a table in your Workbook2 that identifies the first 2 characters of the codes in one column and the associate sheetname in workbook2 (it seems that the first 2 characters identifies the product type).

e.g if you put this list say in N2:O11 of Workbook2, Sheet1

ER Earring 1
BT Necklace 2
NK Necklace 4
RG Ring 8
BR Broosh 13
CF Cufflinks 15
KR Key Ring 18
WC Watch Charm 23
MC Mobile Charm 24
NS Samples 00

Then download the Morefunc.xll addin and use this formula in H2

Please Login or Register  to view this content.

And this formula in I2:

Please Login or Register  to view this content.

and copy down.