Creating a repeating (?) vlookup table for raw materials

[Ikeda]

Hello. I just tried posting this but got an error. Apologies for any possible double post.

I want to achieve the following.

• User selects the item they want in A1 (dropdown menu), called ITEM1
• In the second sheet, there are tables with the following properties such as

    ITEMS    MATERIALS    AMOUNT  
    ITEM1    material1    3  
    ITEM1    material2    5  
    ITEM2    material3    1  
    ITEM3    material4    12

This means that in order to issue an order with ITEM1, the user needs 3 x material1 and 5 x material2.

However, down the list, some materials need further raw materials to create. For instance

    ITEMS              MATERIALS              AMOUNT  
    material1          submaterial1-1           4  
    submaterial1-1      submaterial1-1-1      2

But not all materials have submaterials.

I want to display the total raw materials only.

So when user selects ITEM1 they should get on the same sheet the total amount of raw materials they need:

• submaterial1-1-1 x 24
• Material2 x 5

I can write a simple VLOOKUP if the item does not have any sub-materials, but i just cannot make it go all the way to the very final raw material, which is the only information my users need to see.


Note: The writing of the tables is not set in stone. I can, if needed, adjust them in any fashion.

 

[Ikeda]

Clarification below as requested. User wants to order "Orange Juice with Bacardi". The raw materials for "Orange Juice with Bacardi" is 2 x Oranges, and 1x Bacardi. Assume that we are producing Bacardi as well. In this case, we would hypothetically need 3 units of Alcohol and 2 units of Sugar.
So the raw material breakdown of an "Orange Juice with Bacardi" is: 2x Oranges 3x alcohol 2x sugar
I want the VLOOKUP table to give me just that. I'm using 2 VLOOKUPs, one for the material and one for the amount of items.
<code style="margin: 0px; padding: 1px 5px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; font-size: 13px; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; box-sizing: inherit; background-color: rgb(239, 240, 241); white-space: pre-wrap;">=VLOOKUP($A$1,Materials!$B$2:$D$4, 2, FALSE)</code> and <code style="margin: 0px; padding: 1px 5px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; font-size: 13px; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; box-sizing: inherit; background-color: rgb(239, 240, 241); white-space: pre-wrap;">=VLOOKUP($A$1,Materials!$B$2:$D$4, 3, FALSE)</code>
But I cannot go "one level down further". Currently I would only get
<code style="margin: 0px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; box-sizing: inherit; white-space: inherit;">Oranges 2
Bacardi 1
</code>but I want
<code style="margin: 0px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; box-sizing: inherit; white-space: inherit;">Oranges 2
Alcohol 3
Sugar 2</code>