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

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

<tbody>
</tbody>

 

[Eric W]

Welcome to the board.

Here's one way:

Excel 2012

	A	B	C	D	E	F	G	H	I	J	K
1	Product	Type	Profit	Lbs	Binary	Type	Product	Profit	Lbs
2	Carrots	Vegetable	100	20	1	Fruit				3
3	Apples	Fruit	80	30	1	Fruit				14
4	Bananas	Fruit	65	25	0	Vegetable				2
5	Pork	Meat	90	50	1	Vegetable				13
6	Salmon	Fish	70	35	1	Dairy				19
7	Trout	Fish	80	25	1	Dairy				20
8	Halibut	Fish	45	30	0	Meat				5
9	Oranges	Fruit	25	35	0	Meat				11
10	Beans	Vegetable	25	20	0	Fish				6
11	Beef	Meat	80	40	1	Fish				7
12	Tomato	Vegetable	40	45	0			635	300
13	Lettuce	Vegetable	30	30	1			Maximize	<= 300	Fruit	2
14	Grapes	Fruit	20	25	1					Vegetable	2
15	Catfish	Fish	45	35	0					Dairy	2
16	Chicken	Meat	75	50	0					Meat	2
17	Butter	Dairy	30	20	0					Fish	2
18	Turkey	Meat	55	45	0
19	Cheese	Dairy	35	20	1
20	Yogurt	Dairy	50	25	1
21	Milk	Dairy	75	60	0

<tbody>
</tbody>

Sheet2​

Worksheet Formulas Cell Formula H12 =SUMPRODUCT(C2:C21,$E2:$E21) I12 =SUMPRODUCT(D2:D21,$E2:$E21) K13 =COUNTIFS($B$2:$B$21,J13,$E$2:$E$21,1) <tbody> </tbody>

<tbody>
</tbody>

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

<tbody>
</tbody>

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!