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.

A	B	C	D	E	F	G	H	I
LF-D-1-W-156	1	pump 1
LF-D-1-W-156	1	valve 1		1	2	3	4	5
LF-D-1-W-156	1	filter 1		1	S	1	P	156
LF-D-2-W-156	1	pump 2		2	R	2	W	190
LF-D-2-W-156	1	valve 2		3	D	3
LF-D-2-W-156	1	filter 2		4		4
LF-D-3-W-156	1	pump 3			3	2	1	1
LF-D-3-W-156	1	valve 3
LF-D-3-W-156	1	filter 3		LF	D	2	P	156
LF-D-4-W-156	1	pump 4
LF-D-4-W-156	1	valve 4			LF-D-2-P-156
LF-D-4-W-156	1	filter 4

<tbody>
</tbody>

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