if value is between x and y then ""

[Benaii]

Hello !

I am looking for an excel formula or VBA code for the following :

If value is between x and y , then "1", else if value is between y and z; then "2", else.....

So far I was actually using IF(A1<=E1;1;IF(AND(A1>=E2;A1<=E3);2;IF(AND(..........
So basically, lots of if(and()) imbricated.

This was working well until the point I actually had too many of them for excel...

So now I need to find a new formula. I guess my way of doing this originally was far from optimal but it worked.

Anyone knows of a better way? I guess in VBA lies the answer but I am not trained on it and dont know how to do it..

Thanks a lot in advance, hope this was clear.

 

[Jonmo1]

Welcome to the board.

I'm thinking a lookup or match type of function should do the job.
But it's a little unclear what the relationsips between A1 and the values in column E.

Can you post some sample data, and expected results?

Also which version of XL are you using?