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

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: #D9E1F2, align: center"]INDIVIDUAL MODEL NUMBER[/TD]
[TD="bgcolor: #D9E1F2, align: center"]Quantity Available[/TD]
[TD="bgcolor: #E2EFDA, align: center"]PACKAGE MODEL NUMBER[/TD]
[TD="bgcolor: #E2EFDA, align: center"]VLOOKUP (5 CELLS TOTAL)[/TD]
[TD="bgcolor: #E2EFDA, align: center"]RESULT SHOULD BE [/TD]
[TD="align: center"][/TD]
[TD="bgcolor: #FCE4D6, align: center"]PACKAGE_SINK1 INCLUDES THE FOLLOWING INDIVIDUAL MODEL NUMBERS[/TD]
[TD="bgcolor: #FCE4D6, align: center"]PACKAGE_SINK2 INCLUDES THE FOLLOWING INDIVIDUAL MODEL NUMBERS[/TD]
[TD="bgcolor: #FCE4D6, align: center"]PACKAGE_SINK3 INCLUDES THE FOLLOWING INDIVIDUAL MODEL NUMBERS[/TD]

[TD="align: center"]2[/TD]
[TD="align: center"]FAUCET[/TD]
[TD="align: center"]50[/TD]
[TD="align: center"]PACKAGE_SINK1[/TD]
[TD="align: center"][/TD]
[TD="align: center"]15[/TD]
[TD="align: center"][/TD]
[TD="align: center"]FAUCET[/TD]
[TD="align: center"]FAUCET[/TD]
[TD="align: center"]FAUCET[/TD]

[TD="align: center"]3[/TD]
[TD="align: center"]DRAIN[/TD]
[TD="align: center"]30[/TD]
[TD="align: center"]PACKAGE_SINK2[/TD]
[TD="align: center"][/TD]
[TD="align: center"]10[/TD]
[TD="align: center"][/TD]
[TD="align: center"]DRAIN[/TD]
[TD="align: center"]DRAIN[/TD]
[TD="align: center"]DRAIN[/TD]

[TD="align: center"]4[/TD]
[TD="align: center"]WAX[/TD]
[TD="align: center"]35[/TD]
[TD="align: center"]PACKAGE_SINK3[/TD]
[TD="align: center"][/TD]
[TD="align: center"]5[/TD]
[TD="align: center"][/TD]
[TD="align: center"]WAX[/TD]
[TD="align: center"]WAX[/TD]
[TD="align: center"]WAX[/TD]

[TD="align: center"]5[/TD]
[TD="align: center"]SILICONE[/TD]
[TD="align: center"]15[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]SILICONE[/TD]
[TD="align: center"]SILICONE[/TD]
[TD="align: center"]SILICONE[/TD]

[TD="align: center"]6[/TD]
[TD="align: center"]SINK1[/TD]
[TD="align: center"]20[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]SINK1[/TD]
[TD="align: center"]SINK2[/TD]
[TD="align: center"]SINK3[/TD]

[TD="align: center"]7[/TD]
[TD="align: center"]SINK2[/TD]
[TD="align: center"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: center"]SINK3[/TD]
[TD="align: center"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</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)))