IF and AND functions combined for several ranges

[Pickle Lily]

I would like to write a formula that assigns a number to a cell value based on a range of numbers. For example, I have a column that calculates 'risk percentage', which I then need to assign to a 'risk ranking' as per the table below:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Risk Ranking[/TD]
[TD]Percentage Range[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]<10%[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]10-20%[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]20-40%[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]40-60%[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]>60%[/TD]
[/TR]
</tbody>[/TABLE]


I have written the following formula: =IF(AND(G22>P13,G22<=Q13),O13,R13), where:

G22 = test value (e.g. risk percentage)
P13 = bottom of percentage range (e.g. 0%)
Q13 = top of percentage range (e.g. 10%)
O13 = result if true (e.g. risk ranking)
R13 = result if false (e.g. -)

I have clumsily spread this out over five columns - one for each percentage range, but is there a way to combine this into one formula / cell?

Many thanks,

 

[Eric W]

Welcome to the forum!

Try:

=MATCH(G22,{0,0.1,0.2,0.4,0.6})

 

[wideboydixon]

Swap the order slightly and you can use a VLOOKUP:

Book1
	A	B	C	D	E
1	Percentage Lower Bound	Risk Ranking		Test Value	Risk
2	0%	1		15%	2
3	10%	2
4	20%	3
5	40%	4
6	60%	5
Sheet1

Cell Formulas
Range	Formula
E2	=VLOOKUP($D2,$A$2:$B$6,2,TRUE)

WBD