Formula to populate a table based referencing a cell with a variable output.

[white95gt]

Hello,

I have a sheet with 4 drop down boxes to build a part #. There is a formula that concatenate the multiple answers from drop down boxes to make a part #. F12 is the formula that builds the part number based on the drop down boxes. The items directly above it.

I would like to have a formula in 3 separate cells anywhere on the sheet, not important, to show that once the part # in F12 = LF-D-2-P-156 (in this case) is picked, it would populate all parts and quantities from its respective cells in columns C and B. I would cpy the formula 3 times as required and change the reference cell to show all the items.

Sorry for no grid, i copied from excel and it did not transfer them.

Column A is the complete assembly #, Column B is the quantity, Column C is individual pieces to make the assembly.

[TABLE="width: 0"]
<tbody>[TR]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[TD]E
[/TD]
[TD]F
[/TD]
[TD]G
[/TD]
[TD]H
[/TD]
[TD]I
[/TD]
[/TR]
[TR]
[TD]LF-D-1-W-156
[/TD]
[TD]1
[/TD]
[TD]pump 1
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]LF-D-1-W-156
[/TD]
[TD]1
[/TD]
[TD]valve 1
[/TD]
[TD]
[/TD]
[TD]1
[/TD]
[TD]2
[/TD]
[TD]3
[/TD]
[TD]4
[/TD]
[TD]5
[/TD]
[/TR]
[TR]
[TD]LF-D-1-W-156
[/TD]
[TD]1
[/TD]
[TD]filter 1
[/TD]
[TD]
[/TD]
[TD]1
[/TD]
[TD]S
[/TD]
[TD]1
[/TD]
[TD]P
[/TD]
[TD]156
[/TD]
[/TR]
[TR]
[TD]LF-D-2-W-156
[/TD]
[TD]1
[/TD]
[TD]pump 2
[/TD]
[TD]
[/TD]
[TD]2
[/TD]
[TD]R
[/TD]
[TD]2
[/TD]
[TD]W
[/TD]
[TD]190
[/TD]
[/TR]
[TR]
[TD]LF-D-2-W-156
[/TD]
[TD]1
[/TD]
[TD]valve 2
[/TD]
[TD]
[/TD]
[TD]3
[/TD]
[TD]D
[/TD]
[TD]3
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]LF-D-2-W-156
[/TD]
[TD]1
[/TD]
[TD]filter 2
[/TD]
[TD]
[/TD]
[TD]4
[/TD]
[TD]
[/TD]
[TD]4
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]LF-D-3-W-156
[/TD]
[TD]1
[/TD]
[TD]pump 3
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]3
[/TD]
[TD]2
[/TD]
[TD]1
[/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD]LF-D-3-W-156
[/TD]
[TD]1
[/TD]
[TD]valve 3
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]LF-D-3-W-156
[/TD]
[TD]1
[/TD]
[TD]filter 3
[/TD]
[TD]
[/TD]
[TD]LF
[/TD]
[TD]D
[/TD]
[TD]2
[/TD]
[TD]P
[/TD]
[TD]156
[/TD]
[/TR]
[TR]
[TD]LF-D-4-W-156
[/TD]
[TD]1
[/TD]
[TD]pump 4
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]LF-D-4-W-156
[/TD]
[TD]1
[/TD]
[TD]valve 4
[/TD]
[TD]
[/TD]
[TD][/TD]
[TD="colspan: 4"]LF-D-2-P-156
[/TD]
[/TR]
[TR]
[TD]LF-D-4-W-156
[/TD]
[TD]1
[/TD]
[TD]filter 4
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
</tbody>[/TABLE]

I have tried index, and match, also vlookups with no success.
Maybe a VBA code would work better?


Thanks
Jon

 

[white95gt]

Here is an image of my workbook:

Maybe these images will help? Is this possible in excel?

Jon