Home

Auto select a band

NBaxter
Occasional Visitor

HI

I am hoping someone can help me. 

I have series of bands;

1. <5.9

2. 6-8.9

3. 9-11.9 

4. 12-14.9 etc etc

 

I'd like to be able to insert a number (e.g 7) into a cell and for the adjacent cell to automatically select the band (in this case it would be 2.)

 

Can anyone help with a function/formula? I have seen VLOOKUP but I do not think it is right.

 

Thanks

Nick

2 Replies

Hi Nick,

 

This should work for you, see image

 

=IF(C8<C2,A2,IF(AND(C8>B3,C8<B4),A3,IF(AND(C8>B4,C8<B5),A4,IF(AND(C8>B5,C8<B6),A5,""))))

 

Rich

Another variant, if put series in the range as

image.png

the formula could be

=LOOKUP(A1,$E$1:$E$4,$F$1:$F$4)

or, for this concrete one,

=INT(A1/3)+(A1<3)