Search for datats in different Exel sheets

[Franz Muster]

Hello together,

I have a problem which I can not solve.

I know how I can search and import datas from an other Excel Sheet with
the help of the formula VLOOKUP but I don`t know how I can get the
information I need if I`m search in different Exelsheets for this
information.

So my problem exactly is that I use for different Groups of Products,
different Excel sheets, and I want now creat one sheet in wich I can
insert in one Cell the number of the product and in the other one
automatically appear the price of the product.
So Excel have to search in all of my different sheets to find the
number of the Product with the right price.

I couldn`t until now not find any way to sove this problem, and it is
for me, because of other reasons, not possible to copy all my products
in one exel sheet.

Thanks a lot
Franz

 

[Dave Peterson]

How many sheets do you have?

You could look in each sheet until you find it.

=IF(NOT(ISERROR(VLOOKUP(A1,Sheet2!A:B,2,0))),VLOOKUP(A1,Sheet2!A:B,2,0),
IF(NOT(ISERROR(VLOOKUP(A1,Sheet3!A:B,2,0))),VLOOKUP(A1,Sheet3!A:B,2,0),
IF(NOT(ISERROR(VLOOKUP(A1,Sheet4!A:B,2,0))),VLOOKUP(A1,Sheet4!A:B,2,0),
"missing from all 3")))

(I stopped after looking at 3 sheets. You could continue until you try to add
the 8th lookup--excel can nest 7 functions.)

If the part numbers have some unique identifier (a special prefix that
corresponds to the product), you could use that to determine which sheet to look
through.

 

[vezerid]

Franz,

Domenic has suggested a quite powerful formula for VLOOKUP() over
multiple sheets. Look in microsoft.public.excel.worksheet.functions and
search with keywords
Vlookup over multiple sheets domenic

and I think you will find exactly what you are looking for.

HTH
Kostis Vezerides