Bill of Material Data Look up issue

[jacob@thepenpoint]

Greetings all,

I have an inventory of several hundred raw material items. I have a sheet with all of the raw materials going down the left side and the finished goods going across the top with quantities required for each finished good making up the body of the data. These quantities are then summed to the right to give me my total material requirements.

What I am attempting to do is to extract a concise bill of materials based on the finished good number. What I am afraid of is that I have wandered too far and have entered the realm of Access or some such.

Suggestions? Solutions? Condolences?

Thanks to all!

[Fotis1991]

Hoping that i understand your goal, pls give a try to this.

In B20 and copy down, try this ARRAY formula.

=IFERROR(INDEX($A$7:$A$16,SMALL(IF(B$7:B$16<>"",ROW($A$7:$A$16)-6),ROW(A1))),"")

In C20 and copy down this one.

=IFERROR(INDEX($B$7:$F$16,MATCH(B20,$A$7:$A$16,0),MATCH(B$19,$B$6:$F$6,0)),"")

[jacob@thepenpoint]

Thanks Fotis

I haven't worked with many Array formulas so I will have to play around with this to see exactly what it is doing. It worked perfectly on the first BOM, but I'll need to tweek it to take care of the others it seems.

I am going to call it solved! Thanks again.

[AndyLitch]

BOM MRP.xlsm

How's that..

You need to insert a line of helper cells and unmerge your cells but that's all.

It uses a combination of vlookup,hlookup and offset

[Fotis1991]

You are welcome. The only that you have to do is to change the range in this part of the formula, for the other BOM.

....SMALL(IF(B$7:B$16

[jacob@thepenpoint]

Thanks again Fotis, changing that reference was all it took to work. I guess it is time for me to start working with arrays.

[im.zeeshan]

I'm assuming all your data is stored in Sheet1 as per table in the attachment, only starting from the top most cell i.e. A1.
("RM1" is in Cell A3, "RM2" in cell A4, and so on)

Run the following macro when data has been set:

Your results will be in Sheet2.

Please Login or Register  to view this content.

[Fotis1991]

Thanks again Fotis, changing that reference was all it took to work. I guess it is time for me to start working with arrays.

Here is a very good link to start.

http://www.cpearson.com/excel/ArrayFormulas.aspx