Creating a repeating (?) vlookup table for raw materials

Ikeda

New Member
Joined
Mar 8, 2018
Messages
2
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
Code:
    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
Code:
    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.
 

Some videos you may like

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

Ikeda

New Member
Joined
Mar 8, 2018
Messages
2
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>
 

Watch MrExcel Video

Forum statistics

Threads
1,122,168
Messages
5,594,632
Members
413,919
Latest member
ZaxAlchemist

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top