IF and AND formulas for multiple criteria

[juca73]

Hi All

Is it possible to create a formula using the IF and AND formulas ( or something else ) to return a value from a cell that can fall between one of 10 differing ranges, as a combined formula instead of a differing one for each line

for eg

Cell A2 = 92.35%
Cell A3 = 35.69%
Cell A4 = 12.54%

In the adjacent B column and cells 2,3 and 4 i want to return the range that percentage figure falls into which are

0-10
11-20
21-30
31-40
41-50
51-60
61-70
71-80
81-90
91-100

THerefore
Cell A2 = 92.35% Cell B2 = 91-100
Cell A3 = 35.69% Cell B3 = 31-40
Cell A4 = 12.54% Cell B4 =11-20

Thanks in advance

 

[Peter_SSs]

If cell A2 was 90.35% instead of 92.35% then it would not fit into 91-100 or 81-90
Perhaps you need a more precise rule?

 

[etaf]

you can use a lookup table
=INDEX($G$2:$G$11,MATCH(A2*100,$F$2:$F$11,1))

Book1
	A	B	C	D	E	F	G
1
2	0.9235	91-100				0	0-10
3	0.3569	31-40				11	11-20
4	0.1254	11-20				21	21-30
5						31	31-40
6						41	41-50
7						51	51-60
8						61	61-70
9						71	71-80
10						81	81-90
11						91	91-100
12
13
14	Cell A2 = 92.35%
15	Cell A3 = 35.69%
16	Cell A4 = 12.54%
Sheet1

Cell Formulas
Range	Formula
B2:B4	B2	=INDEX($G$2:$G$11,MATCH(A2*100,$F$2:$F$11,1))

 

[SanjayGMusafir]

I think lookup function can serve the purpose, if you can make a range to lookup and return value. Remember 95% Excel recognizes as 0.95 to explain.

 

[juca73]

Thanks etaf, works perfectly

 

[etaf]

you are welcome

 

[Peter_SSs]

works perfectly

So then you are happy with the result of 81-90 in row 5 here even though 90.99 is not in the range 81-90?

24 03 21.xlsm
	A	B			E	F	G
1
2	92.35%	91-100				0	0-10
3	35.69%	31-40				11	11-20
4	12.54%	11-20				21	21-30
5	90.99%	81-90				31	31-40
6						41	41-50
7						51	51-60
8						61	61-70
9						71	71-80
10						81	81-90
11						91	91-100
Range

Cell Formulas
Range	Formula
B2:B5	B2	=INDEX($G$2:$G$11,MATCH(A2*100,$F$2:$F$11,1))