Vacation hours calculated by years of service

[mmaffe23]

I have two groups of ee's that have different vacation schedules. I want to use an if statement and a vlookup to determine the correct amount of vacation time for each group. What is the best way to accomplish this. I have attached a partial view of my spreadsheet data. Thanks....

MGR
1 yr 80 HRS
2-4 yrs 120 HRS
5+yrs 160 HRS

REG
1 yr 40 HRS
2-4 yrs 80 HRS
5-7 yrs 120 HRS
8+ yrs 160 HRS

 

[DRSteele]

Welcome to the forum.

I think this is what you're after. I don't know what an ee is nor what groups of them are.

MrExcel posts17_a.xlsx
	A	B	C	D	E	F	G	H	I
5	employee	service	vacation
6	manager	0.5	0
7	manager	0	0
8	manager	1	80		manager			workman
9	manager	2	120		0			0
10	manager	3	120		1	80		1	40
11	manager	4	120		2	120		2	80
12	manager	5	160		5	160		5	120
13	manager	6	160					8	160
14	workman	0.5	0
15	workman	1	40
16	workman	2	80
17	workman	3	80
18	workman	4	80
19	workman	5	120
20	workman	6	120
21	workman	8	160
22	workman	9	160
Sheet66

Cell Formulas
Range	Formula
C6:C22	C6	=IF(A6="manager",VLOOKUP(B6,$E$9:$F$12,2,TRUE),VLOOKUP(B6,$H$9:$I$13,2,TRUE))

 

[mmaffe23]

Thank you, this is exactly what I needed to do!

 

[DRSteele]

You're welcome. I'm glad it works for you.