Excel - solve multiple criteria

Question

Hi

Currently, I had create the table as shown in Table 1; however I been facing difficulties insert and create the formula with Vlookup, Match, Index, OR, AND, & If and etc solve my results as listed below:

1. Under the Cell B19 & B20 straight forward to insert the drop down list to look for the product name and items respectively
2. Under the Cell B21 with tie up condition 1 to meet the “Min”& “Max”
3. Under the Cell B22 with tie up condition 2 of profit 1, profit 2 & profit 3
4. Under the Cell B23 with tie up condition 1 to meet the “Min”& “Max”, and condition 2 of profit 1, profit 2, profit 3 & profit 4

 Great appreciate that someone who can help 

Table 1

	A	B	C	D	E	F
1	Product Name	A	B	C	D	E
2	Item 1	$100	$106	$111	$116	$121
3	Item 2	$102	$107	$112	$117	$122
4	Item 3	$104	$108	$113	$118	$123
5	Item 4	$106	$109	$114	$119	$124
6	Item 5	$108	$110	$115	$120	$125
7
8	Condition 1
9	Product Name	Min	Max
10	A & C	$100	$110
11	B, D & E	$100	$120
12
13	Condition 2
14	Product Name	Percentages
15	Profit	Profit 1	Profit 2	Profit 3	Profit 4
16	A & C	100%	40%	40%	20%
17	B, D & E	100%	60%	25%	15%
18
19	Product Name
20	Item
21	Cost Meet Condition 1
22	Cost Meet Condition 2
23	Cost Meet Condition 1 & 2

Answer 1

Hi V_Agarwal

Thank for your concern, example in cell B19=A and B20=$100, and this 2 row must meet the condition 1 (row 10 & 11) of Min=$100 & Max=$110 for result of B21, next for cell B22 result is to contribution of profit in condition 2 (row 16 or 17) of profit 1 to 4 respectively and the result of cell 23 to meet both of them

Steven

Answer 2

Give some samples in row 19 and 20 and then what is your expected result in Cell B21 to F23.