some time ago i was asking similar question in here 3D formula with the same position of cells but now i am facing much more difficult problem. I have a sheet named Storage and other sheets called simply shop1, shop2.
In storage i have all of my good which are sold but not in every shop i sell the same and all goods. so in some shop you dont find specific good for example. And now what i want. In every sheet i store number of sold goods and in storage i would like to have the SUM number of all sold goods. so i would lookup if an item from the storage is sold in each shop and if yes, it would lookup the number and add it to sum.
it is easy if the items from the storage would be always on the same row. for example the sum for all items on the row 2 would be
=SUM(Shop1:Shop4!C2)
but there would be plenty white and waste places because the shop sell only part of goods from storage. so now i would like to use VLOOKUP function into it. i thaught i can use
=SUM(VLOOKUP(A2;shop1:shop2!A:B;2;FALSE))
but this formula gives me error message. so how to give 3D formula into VLOOKUP function?
thanks for answer
Edit: I want to add that an item from the storage is not always on the same row on each shop, so for example item1 can be on the third row in shop1 sheet but on the 10th row on the shop2 sheet and in the shop3 sheet you will not even find item1.
