How to use Excel Solver with lookups?

[phort]

Is there a way to use the solver with the data shown below? I want to have the Solver Table (Columns F-I) pull the data (Columns A-D) with the following constraints. 1) Maximize the sum of the profit (Column H), 2) Keep the sum of the Lbs <= 300 (Column I), 3) Each type must include two different products

[TABLE="width: 500"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]G[/TD]
[TD="align: center"]H[/TD]
[TD="align: center"]I[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD="align: center"]Product[/TD]
[TD="align: center"]Type[/TD]
[TD="align: center"]Profit[/TD]
[TD="align: center"]Lbs[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]Type[/TD]
[TD="align: center"]Product[/TD]
[TD="align: center"]Profit[/TD]
[TD="align: center"]Lbs[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Carrots[/TD]
[TD]Vegetable[/TD]
[TD]100[/TD]
[TD]20[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Fruit[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Apples[/TD]
[TD]Fruit[/TD]
[TD]80[/TD]
[TD]30[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Fruit[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Bananas[/TD]
[TD]Fruit[/TD]
[TD]65[/TD]
[TD]25[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Vegetable[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Pork[/TD]
[TD]Meat[/TD]
[TD]90[/TD]
[TD]50[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Vegetable[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Salmon[/TD]
[TD]Fish[/TD]
[TD]70[/TD]
[TD]35[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Dairy[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Trout[/TD]
[TD]Fish[/TD]
[TD]80[/TD]
[TD]25[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Dairy[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]Halibut[/TD]
[TD]Fish[/TD]
[TD]45[/TD]
[TD]30[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Meat[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]Oranges[/TD]
[TD]Fruit[/TD]
[TD]25[/TD]
[TD]35[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Meat[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]Beans[/TD]
[TD]Vegetable[/TD]
[TD]25[/TD]
[TD]20[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Fish[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]Beef[/TD]
[TD]Meat[/TD]
[TD]80[/TD]
[TD]40[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Fish[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]Tomato[/TD]
[TD]Vegetable[/TD]
[TD]40[/TD]
[TD]45[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]=sum(H2:H11)[/TD]
[TD]=sum(I2:I11)[/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]Lettuce[/TD]
[TD]Vegetable[/TD]
[TD]30[/TD]
[TD]30[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Maximize[/TD]
[TD]<= 300[/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]Grapes[/TD]
[TD]Fruit[/TD]
[TD]20[/TD]
[TD]25[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD]Catfish[/TD]
[TD]Fish[/TD]
[TD]45[/TD]
[TD]35[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD]Chicken[/TD]
[TD]Meat[/TD]
[TD]75[/TD]
[TD]50[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]17[/TD]
[TD]Butter[/TD]
[TD]Dairy[/TD]
[TD]30[/TD]
[TD]20[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]18[/TD]
[TD]Turkey[/TD]
[TD]Meat[/TD]
[TD]55[/TD]
[TD]45[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]19[/TD]
[TD]Cheese[/TD]
[TD]Dairy[/TD]
[TD]35[/TD]
[TD]20[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]20[/TD]
[TD]Yogurt[/TD]
[TD]Dairy[/TD]
[TD]50[/TD]
[TD]25[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]21[/TD]
[TD]Milk[/TD]
[TD]Dairy[/TD]
[TD]75[/TD]
[TD]60[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Eric W]

Welcome to the board.

Here's one way:

Excel 2012

	A	B	C	D	E	F	G	H	I	J	K
Carrots	Vegetable	Fruit
Apples	Fruit	Fruit
Bananas	Fruit	Vegetable
Pork	Meat	Vegetable
Salmon	Fish	Dairy
Trout	Fish	Dairy
Halibut	Fish	Meat
Oranges	Fruit	Meat
Beans	Vegetable	Fish
Beef	Meat	Fish
Tomato	Vegetable
Lettuce	Vegetable	Maximize	<= 300	Fruit
Grapes	Fruit	Vegetable
Catfish	Fish	Dairy
Chicken	Meat	Meat
Butter	Dairy	Fish
Turkey	Meat
Cheese	Dairy
Yogurt	Dairy
Milk	Dairy

<tbody>
[TD="align: center"]1[/TD]
[TD="align: center"]Product[/TD]
[TD="align: center"]Type[/TD]
[TD="align: center"]Profit[/TD]
[TD="align: center"]Lbs[/TD]
[TD="align: center"]Binary[/TD]
[TD="align: center"]Type[/TD]
[TD="align: center"]Product[/TD]
[TD="align: center"]Profit[/TD]
[TD="align: center"]Lbs[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]100[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]3[/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]80[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]14[/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]65[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]0[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]

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

[TD="align: right"]90[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]13[/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]70[/TD]
[TD="align: right"]35[/TD]
[TD="align: right"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]19[/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]80[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]20[/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]45[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]0[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]5[/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]

[TD="align: right"]25[/TD]
[TD="align: right"]35[/TD]
[TD="align: right"]0[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]11[/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]

[TD="align: right"]25[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]0[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]6[/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]

[TD="align: right"]80[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]7[/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]

[TD="align: right"]40[/TD]
[TD="align: right"]45[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]635[/TD]
[TD="align: right"]300[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]

[TD="align: right"]30[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]2[/TD]

[TD="align: center"]14[/TD]

[TD="align: right"]20[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]2[/TD]

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

[TD="align: right"]45[/TD]
[TD="align: right"]35[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]2[/TD]

[TD="align: center"]16[/TD]

[TD="align: right"]75[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]2[/TD]

[TD="align: center"]17[/TD]

[TD="align: right"]30[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]2[/TD]

[TD="align: center"]18[/TD]

[TD="align: right"]55[/TD]
[TD="align: right"]45[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]19[/TD]

[TD="align: right"]35[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]20[/TD]

[TD="align: right"]50[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]21[/TD]

[TD="align: right"]75[/TD]
[TD="align: right"]60[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet2​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH]H12[/TH]
[TD="align: left"]=SUMPRODUCT(C2:C21,$E2:$E21)[/TD]
[/TR]
[TR]
[TH]I12[/TH]
[TD="align: left"]=SUMPRODUCT(D2:D21,$E2:$E21)[/TD]
[/TR]
[TR]
[TH]K13[/TH]
[TD="align: left"]=COUNTIFS($B$2:$B$21,J13,$E$2:$E$21,1)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<tbody>[TR]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH]J2[/TH]
[TD="align: left"]{=SMALL(IF($B$2:$B$21=F2,IF($E$2:$E$21=1,ROW($B$2:$B$21))),COUNTIF($F$2:$F2,F2))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]



Put the heading "Binary" in E1. We'll use E2:E21 as a 0 or 1 to indicate whether we use this row or not. Then put the formulas in H12 and I12. Then put the categories in J13:J17. Put the formula in K13 and drag down to K17.

Now click Solver.

Maximize: H12
By Changing: E2:E21
Subject to Constraints:
$E$2:$E$21 = binary
$I$12 <= 300
$K$13:$K$17 = 2
Solving Method: Evolutionary.

Click Solve and wait a few moments. You should get the results above. This does not use any formulas in G2:I11. You can look at column E to see which ones are used. If you want to fill out the G2:I11 table, you can use the formula in J2 to get the appropriate row number, then put in INDEX formulas in G2:I11.

Good luck!