Simple (and stupid) IF question -get value if number is between 1-4,4-8 etc..

[whocares4]

Iīm trying to get excel to insert a given value based on some criterias..should be simple, but i canīt get it working
If R2 is <4 it should get the value W2,4-7 should get X2, 8-11 should get Y2 and so on.
I have it working up till 7, but when i get 8, it doesnīt change value?

Please Login or Register  to view this content.

As i said, should be simple, but itīs driving me mad

[Jdevil]

You're doing it in the wrong order. As you typed it, as long as R2 >= 4, it will stop there since it meets the conditions.

Do it backwards.

=IF(R2>=4;(X2);IF(R2>=8;(Y2);IF(R2>=12;(Z2);IF(R2>=16;(AA2);W2))))
=IF(R2>=16;AA2;IF(R2>=12;Z2;IF(R2>=8;Y2;IF(R2>=4;X2;W2))))

[Jonmo1]

Or don't even use IF to begin with.
Use LOOKUP instead..

=LOOKUP(R2,{0,4,8,12,16},W2:AA2)

Or since you have such a nice even pattern of 4's, try

=INDEX(W2:AA2,INT(R2/4)+1)

[ggs22]

I think its because for a given value, multiple conditions are met at the same time and Excel returns the value associated to the first condition. e.g. if R2=13 then R2>=8 = true AND R2>=12= true thus the return value is 8. Try:

Please Login or Register  to view this content.

[whocares4]

Thanks both of you! As I said simple, but I had been staring me blind on it.
The Index function is neat..hadnīt thought of that

[Jonmo1]

You're welcome.