Multiple ifs to return a value

[ajaay]

G'day

I would like to make my excel sheet return a value when one of 4 conditions is true. I only know how to do it if one is so i was hoping someone could help me. Basically it's like this:

If the value of the cell A1 is >0 but <5 return 28.54
If the value of the cell A1 is >5.001 but <12 return 23.42
If the value of the cell A1 is >12.001 but <20 return 28.54
If the value of the cell A1 is >20.001 but <999 return 28.54

i would like it so it works in one cell? is there anyway to do this? I am quite a novice as this hahah any help would be greatly appreciated. The only way i could think of can't take all these values so i have no idea really on what to do. I think i have provided enough information, if i need more let me know.

Thanks A-J

 

[Armando Montes]

Try this:
=LOOKUP(A1,{0,5.001,12.001,20.001},{28.57,23.42,28.54,28.54})

 

[Haseeb Avarakkan]

Hello Try,

=IF(AND(A1>5.001,A1<12),23.42,IF(AND(A1>0,A1<999),28.54,""))

 

[timbailey3]

When you say "If the value of the cell A1 is >0 but <5 return 28.54"

Do you want excel to 28.54 if the number is anywhere from 0 to 5 (I.E. including 0 and 5), or do you want it to return 28.54 only if it is greater than 0 (I.E. 0.0001 but less than 5 (I.E. 4.9999). This the difference between the two will have an impact on what excel returns.

If you want "greater than 0 (I.E. 0.001 but less than 5 (I.E. 4.999)" you would want to use:

=(if(and(A1>0,A1<5),28.54,if(and(A1>5.001,A1<12),23.42,if(and(A1>12.001,A1<20),28.54,if(and(A1>20.001,A1<999),28.54,)

However, if you want "anywhere from 0 to 5 (I.E. including 0 and 5)" you would need to modify the formula to:

=(if(and(A1>=0,A1<=5),28.54,if(and(A1>=5.001,A1<=12),23.42,if(and(A1>=12.001,A1<=20),28.54,if(and(A1>=20.001,A1<=999),28.54,)