Lookup help - can lookup address values that fall within a range?

Dear all,

I am trying to use LOOKUP to address values that fall within a range e.g. LOOKUP(F2,{"1000<F2<2000","2000<F2<3000","3000<F2<4000","4000<F2<5000","5000<F2<6000","6000<F2<7000"}, {1,2,3,4,5,6}). However, this formula doesn't work.

The F column contains module codes e.g. 1103, 4105, 6107, etc. If the module code starts with 1, it is a Year 1 module, if it starts with 2 it is a Year 2 module and so on an so forth. I would like the Year to be reflected in another column using LOOKUP.

Thanks for offering your suggestions

Whoops, I did a little more experimenting and realised I could use LEFT fx!

Thank you so much Tony.

Just another quick question - If the module code were displayed as AR1301, could I use something like MID fx to copy the first"1" into another column?

Thanks v much for your help.

Thanks Tony. Lem, both your suggestions work too. Thanks v much, lots for me to learn.