Formula help - IF, OR, AND, VLookup? multiple conditions

[EMY245]

Hi all,
I have the following fields of data:

A2 'values': (shows the value 'low', 'mid' or 'high)
B2 ' ratings': (shows a value rating of between 1-5)

I need a formula to populate the %'s in the table below, based on the rating and value. I.e. If Rating is 1 and value is 'low' show 0, if rating is 2 and value is 'low' show 0.5 - is there a quick way to do this without typing out each individual scenario for an incredibly long formula?

Rating	Low (%)	Mid (%)	High (%)
1	0	0	0
2	0.5	0.5	0
3	1	0.5	0
4	2	2	1
5	2.5	3	3.5

<tbody>
</tbody>

 

[AliGW]

There may be, but it really isn't at all clear what the logic is for the values beyond what you have already told us.

 

[FormR]

Hi, maybe you adapt something like this:

Excel 2013
	A	B	C	D	E	F	G	H
1	Value	Rating	%		Rating	Low	Mid	High
2	Mid	3	0.5		1	0	0	0
3	High	4	1		2	0.5	0.5	0
4	Low	5	2.5		3	1	0.5	0
5	Low	1	0		4	2	2	1
6					5	2.5	3	3.5
Sheet1

Cell Formulas
Range	Formula
C2	=INDEX($F$2:$H$6,MATCH(B2,$E$2:$E$6,0),MATCH(A2,$F$1:$H$1,0))

 

[Tetra201]

... is there a quick way to do this without typing out each individual scenario for an incredibly long formula?

Her it is. Place this moderate-length formula in cell B2 and copy across and down:

=ROUND(SUMPRODUCT(CHOOSE($A2,{1,1,1,1,1},{16,8,4,2,1},{81,27,9,3,1},{256,64,16,4,1},{625,125,25,5,1}),CHOOSE(MATCH(B$1,{"Low (%)","Mid (%)","High (%)"},0),{-0.0625,0.7083,-2.6875,4.5417,-2.5},{-0.1667,2,-8.0833,13.25,-7},{-0.0208,0.375,-1.7292,2.875,-1.5})),1)