Adding a table into a table? Vlookup, Excel Table Function (With example)

Hi all,

Could you help me please?
I am trying to create a sheet with subgroups, and in each subgroup there are several pieces, each piece has a number value assigned to it (in the example its their individual price) a quantity and there is a calculation of the total (price in this case) and finally a full subgroup total calculation. - See the example attached!

What I'm trying to do, is (eventually) write a macro, that can add a piece into a chosen subgroup, for example, add "Dessert Forks" into SubGroup 2 or "Cutlery", but for this I somehow have to explain to excel, that there are subgroups, and in the subgroups there are pieces, so I thought I can do it with excel's table function and say there is a table within another table, but that doesn't seem to be possible. As "tables can't overlap". The most important thing about this, is for it to be dynamic, both the subgroups if we add a new subgroup, and the pieces in the subgroups. That's why I thought of the table function, but like I said, that doesn't do the trick.. Or I'm doing it wrong.. Or I'm over complicating.

Any ideas how to explain this to excel? NOT in Macros, just for a VLOOKUP.

I am only recently getting into excel in detail, so please explain your method if you manage to solve the riddle, as I would like to learn from it!

Thank you!!

I need to display item quantities at all times, and if I do it with pivot tables, I get an unnecessary sum, which will be the sum of quantity... and for the original project with 600 rows and 20 groups, any unnecessary data will just cause confusion, so I'm afraid this wouldn't be the best solution, but thank you very much for your time and reply!

How do I do that, also instead of the subtotal is it possible to add another calculation?

Okay, I'm posting the example again with an added column, which will need a function that tells us, based on each part, if there is an extra charge for packaging or not. Not perfectly life-like but lets say the company we are buying the pieces from, won't charge us for the packaging if its only a few items, but will charge us for it if most of them need bubble wrap.

I really can't post my actual data as it is confidential, and it is VERY difficult to bring a "parallel" to it.

So, on Sheet 1 my original layout for which I would like the VLOOKUP functions both to be with dynamic ranges, described for each coloured bit exactly what I would like from it, and on
sheet 2 the pivot table with every column I need to display, and at the bottom, what sums I do not need from it, and for the new column, what I would like the rows to display.

I'm really not sure if this made it any easier to understand, but here it is...

INPUT OUTPUT - Wise: the individual parts are the inputs, there are several questions we need to answer about every part, each of them are user inputs (individual price, quantity needed, sum of price (this is a calculation but still an input!), fragile or not, what colour, used daily or not - for this example) and excel should output information about the subgroup (price to buy the parts in the necessary quantity, there is extra charge for packaging or not, parts are all the same colour or not - for this example) then the outputs from the SUBgroups will be the inputs for the group. (Lets say if the crockery is red we will have to buy red curtains for the restaurant)

Sorry that its so hazy..

I google-d it a bit more what I am trying to do is I think to nest a table within another table. Using excels table function. Is it possible? If so how?

Thank you! I will look into that now!