Vlookup or IF statement

[GuinnessT]

Hi,

I want to assign a group to each of my data columns

eg I have employees years of service and I want to add a group such as 1-5
years 6-10 years etc

Name Years of Service Group
J Blogs 2.5 1 - 5 years
S Smith 11 11-15 years

The only way I can think of doing it is by using IF statments to say if the
value is between 1 and 5 put '1-5 years' etc but this would need more than 7
nested IF's and I have been told that if you need more than 7 there is a
better way of doing it!

Could a vlookup work and if so, how do you get it to look at values within a
range?

Thanks

 

[Bob Phillips]

Create a table somewhere with bottom of range and accompanying text

Years Service
0 <=1
2 2-5
6 6-10
etc.

then use

=VLOOKUP(B2,L1:M10,2)

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[akphidelt]

This is kind of a small work around but you can use this formula to get the
results that you want based off 5 years

=IF((A1/5)<1,"1 - 5 years",INT(A1/5)*5+1&" - "&INT(A1/5)*5+5)

 

[Pete_UK]

Set up a simple table showing the start of the range and the group
name:

1 1 - 5 yrs
6 6 - 10 yrs
11 11 - 15 yrs
etc.

Suppose you put this in X1:Y10.

Then with your Years of Service in column B, put this in C2:

=VLOOKUP(B2,$X$1:$Y$10,2)

and copy it down as required.

If you always have 5 year ranges in the group then an alternative
would be to calculate the group name from the years of service, so
that you wouldn't need a table, but the table approach is more
flexible.

Hope this helps.

Pete

 

[akphidelt]

Alright, sorry about this, but I felt a little bad about the code and
actually tried it out. It does work except if a value is 15, it will select
16-20 years. So to correct that, use this equation instead and you should be
good

=IF((A1/5)<1,"1 - 5 years",IF(MOD(A1/5,1)=0,A1/5*5-4&" - "&INT(A1/5)*5&"
years",INT(A1/5)*5+1&" - "&INT(A1/5)*5+5&" years"))

 

[GuinnessT]

Thanks Akphidelt, great solution works great

Jenny