Categorys per job title and Years of service

Charlene Durand

Board Regular
Joined
Sep 19, 2015
Messages
98
Office Version
  1. 365
Platform
  1. Windows
I need a formula to help me with the category's to be placed into a coloum per person:

Increases was done in June, employees were split into job descriptions & period of service


Category A 0 – 2 years
Category B 2 - 5 years
Category C greater than 5 years
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
how is the data setout
is it a join date
or number for years of service ?
what columns are used
 
Upvote 0
The column's are set out like this

Job title = Z
Years of service = BO

then i need the answer in column:
Grading Level (Category) = AK
 
Upvote 0
what will be in BO

lets say the guy has been with the company 5 years 3 months

will BO contain
5 or 6
or what exactly

you just need a lookup
 
Upvote 0
well it must also go with the job title so if there is 4 Apprentice Mechanic

the alphabet will be in column BO:

A 0 – 2 Years of service
B 2 - 5 Years of service
C 5 + Years of service
 
Upvote 0
sorry more unsure of how your data looks
you also haven't answered my question
how do we know the years of service
how many job title

set up a table

column A = job title
column B = years of service groups ie 0, 2, 5
column C = grade

now we can lookup the grade based on years and title

see next post
 
Upvote 0
Sheet1

ABCD
1titlejob title 1
2years service3
3gradeb
4
5DescriptionMin years serviceMax years servicegrade
6job title 102a
7job title 135b
8job title 15100c
9job title 202c
10job title 235b
11job title 25100a

<tbody>
</tbody>

Spreadsheet Formulas
CellFormula
B3=LOOKUP(B2,OFFSET(B5:D11,MATCH(B1,A6:A11,0),,COUNTIF(A6:A11,B1)))

<tbody>
</tbody>

<tbody>
</tbody>


Excel tables to the web >> Excel Jeanie HTML 4
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,251
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top