Feb 11 2019 05:33 AM
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
Feb 11 2019 08:04 AM
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
Feb 11 2019 01:45 PM
Another variant, if put series in the range as
the formula could be
=LOOKUP(A1,$E$1:$E$4,$F$1:$F$4)
or, for this concrete one,
=INT(A1/3)+(A1<3)