Return a value if two conditions met

[BOYS82]

[TABLE="width: 382"]
<tbody>[TR]
[TD="class: xl409, width: 124, bgcolor: transparent"]PDR rating
[/TD]
[TD="class: xl410, width: 78, bgcolor: transparent"]OP
[/TD]
[TD="class: xl410, width: 64, bgcolor: transparent"] VE
[/TD]
[TD="class: xl411, width: 78, bgcolor: transparent"]EP/DP
[/TD]
[TD="class: xl411, width: 64, bgcolor: transparent"]MP
[/TD]
[TD="class: xl411, width: 100, bgcolor: transparent"]PP
[/TD]
[/TR]
[TR]
[TD="class: xl412, bgcolor: transparent"]% Bonus payable
[/TD]
[TD="class: xl412, bgcolor: transparent"][/TD]
[TD="class: xl412, bgcolor: transparent"][/TD]
[TD="class: xl412, bgcolor: transparent"][/TD]
[TD="class: xl412, bgcolor: transparent"][/TD]
[TD="class: xl412, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl412, bgcolor: transparent"]CBD
[/TD]
[TD="class: xl413, bgcolor: transparent, align: right"]12.00%
[/TD]
[TD="class: xl413, bgcolor: transparent, align: right"]10.00%
[/TD]
[TD="class: xl413, bgcolor: transparent, align: right"]8.00%
[/TD]
[TD="class: xl413, bgcolor: transparent, align: right"]4.00%
[/TD]
[TD="class: xl413, bgcolor: transparent, align: right"]0.00%
[/TD]
[/TR]
[TR]
[TD="class: xl412, bgcolor: transparent"]CBM
[/TD]
[TD="class: xl413, bgcolor: transparent, align: right"]9.00%
[/TD]
[TD="class: xl413, bgcolor: transparent, align: right"]7.50%
[/TD]
[TD="class: xl413, bgcolor: transparent, align: right"]6.00%
[/TD]
[TD="class: xl413, bgcolor: transparent, align: right"]3.00%
[/TD]
[TD="class: xl413, bgcolor: transparent, align: right"]0.00%
[/TD]
[/TR]
[TR]
[TD="class: xl412, bgcolor: transparent"]CDE / CDM
[/TD]
[TD="class: xl413, bgcolor: transparent, align: right"]5.00%
[/TD]
[TD="class: xl413, bgcolor: transparent, align: right"]4.00%
[/TD]
[TD="class: xl413, bgcolor: transparent, align: right"]3.00%
[/TD]
[TD="class: xl413, bgcolor: transparent, align: right"]1.00%
[/TD]
[TD="class: xl413, bgcolor: transparent, align: right"]0.00%
[/TD]
[/TR]
</tbody>[/TABLE]

I need to return a value if certain crietria is met i.e OP & CBD, however the cell i return is a drop down so the user can select the PDR rating, therefore they can choose if a CBD is a OP, VE, EP etc rating so a fixed formaula will not work and thats where i am getting stuck?
Please can anyone help me wit this?

 

[DRSteele]

Is this what you had in mind? Cells B8 and D8 are controlled by Data Validation: select the %Bonus and select the PDR rating and the formula will find the appropriate rate.

Excel 2012
	A	B	C	D	E	F
1		PDR rating
2	% Bonus payable	OP	VE	EP/DP	MP	PP
3	CBD	12%	10%	8%	4%	0%
4	CBM	9%	8%	6%	3%	0%
5	CDE / CDM	5%	4%	3%	1%	0%
6
7		% Bonus payable		PDR rating
8	choose:	CBD		OP
9	result:				12%
10
11	http://www.mrexcel.com/forum/excel-questions/856270-return-value-if-two-conditions-met.html
Sheet14

Cell Formulas
Range	Formula
E9	=INDEX($B$3:$F$5,MATCH($B$8,$A$3:$A$5,0),(MATCH($D$8,$B$2:$F$2,0)))

 

[BOYS82]

Is this what you had in mind? Cells B8 and D8 are controlled by Data Validation: select the %Bonus and select the PDR rating and the formula will find the appropriate rate.

Excel 2012

	A	B	C	D	E	F
PDR rating
% Bonus payable	OP	VE	EP/DP	MP	PP
CBD
CBM
CDE / CDM
% Bonus payable	PDR rating
choose:
result:
http://www.mrexcel.com/forum/excel-questions/856270-return-value-if-two-conditions-met.html

<colgroup><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]

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

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

[TD="align: right"]12%[/TD]
[TD="align: right"]10%[/TD]
[TD="align: right"]8%[/TD]
[TD="align: right"]4%[/TD]
[TD="align: right"]0%[/TD]

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

[TD="align: right"]9%[/TD]
[TD="align: right"]8%[/TD]
[TD="align: right"]6%[/TD]
[TD="align: right"]3%[/TD]
[TD="align: right"]0%[/TD]

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

[TD="align: right"]5%[/TD]
[TD="align: right"]4%[/TD]
[TD="align: right"]3%[/TD]
[TD="align: right"]1%[/TD]
[TD="align: right"]0%[/TD]

[TD="align: center"]6[/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"]7[/TD]
[TD="align: right"][/TD]

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

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

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

[TD="bgcolor: #FFE699"]CBD[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FFE699"]OP[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

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

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #A9D08E, align: right"]12%[/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/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"]11[/TD]

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

</tbody>

Sheet14​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10, bgcolor: #DAE7F5"]E9[/TH]
[TD="align: left"]=INDEX($B$3:$F$5,MATCH($B$8,$A$3:$A$5,0),(MATCH($D$8,$B$2:$F$2,0)))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Yes this is exactly what im looking for thanks so much, although i keep getting a return value of #NA? Do you ahve any idea why this would be the case?

Many thanks