Combination vlookup

[andoano]

Hello,
I'm trying to figure out if this is possible. I want to use a vlookup (or any formula) to return the lowest value of 5 specific values. We have 7 INDIVIDUAL MODEL NUMBERS. Then we have PACKAGE MODEL NUMBERS that include 5 of the INDIVIDUAL MODEL NUMBERS. In (Column D, Rows 2,3,4) I would like the formula to return the lowest value of the 5 INDIVIDUAL MODEL NUMBERS that make up the 1 PACKAGE MODEL NUMBER.

Here is an example:
Excel 2012

	A	B	C	D	E	F	G	H	I
1	INDIVIDUAL MODEL NUMBER	Quantity Available	PACKAGE MODEL NUMBER	VLOOKUP (5 CELLS TOTAL)	RESULT SHOULD BE		PACKAGE_SINK1 INCLUDES THE FOLLOWING INDIVIDUAL MODEL NUMBERS	PACKAGE_SINK2 INCLUDES THE FOLLOWING INDIVIDUAL MODEL NUMBERS	PACKAGE_SINK3 INCLUDES THE FOLLOWING INDIVIDUAL MODEL NUMBERS
2	FAUCET	50	PACKAGE_SINK1		15		FAUCET	FAUCET	FAUCET
3	DRAIN	30	PACKAGE_SINK2		10		DRAIN	DRAIN	DRAIN
4	WAX	35	PACKAGE_SINK3		5		WAX	WAX	WAX
5	SILICONE	15					SILICONE	SILICONE	SILICONE
6	SINK1	20					SINK1	SINK2	SINK3
7	SINK2	10
8	SINK3	5

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

PACKAGES VLOOKUP​

Does anyone have any ideas on how to accomplish this formula? Is this even possible?

 

[DRSteele]

I had to make assumptions here. Every Package contains faucet, drain, wax and silicon. Every 'Package_SinkX' is spelled with a "_" and then "Sink" with a number following up to ten digits long. The rest of the columns are not used in this formula. Put it in D2 and copy down.

=MIN($B$2:$B$5,INDEX($B$6:$B$8,MATCH(MID(C2,SEARCH("_",C2)+1,10),$A$6:$A$8,0)))