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:

Risk Ranking	Percentage Range
1	<10%
2	10-20%
3	20-40%
4	40-60%
5	>60%

<tbody>
</tbody>

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