Excel 2007 : [SOLVED] 4 Option If function

[lchiavelli]

Could someone please help me write an if statement that accomplishes the following.

If I26 is less than 30% enter 0

If I26 is greater than 30% but less than 35% enter 2

If I26 is greater than 35% but less than 40% enter 3

If I26 is greater than 35% enter 4

I have been spinning my wheels on this and I keep getting errors.

Thank you,
Luke

[daddylonglegs]

I assume the last one is supposed to be > 40%. Try this

=LOOKUP(I26*100,{0,30,35,40;0,2,3,4})

On the boundary, e.g. 30%, you'll get the higher band.....

[lchiavelli]

That worked perfect and yes you are right, it should have read 40%. Thank you so much! I was way off based with what I was trying.

[Haseeb Avarakkan]

Also, with MATCH ?

=MATCH(I26*100,{0,30,30,35,40})-1

Since array is an ascending order added an extra 30.