Multiple If Then Formula

[jgharbawi]

I'm trying to figure out a smile formula to calculate the accrual rate "Column D" for an employee based on the years of service. I would like it to either search the table (last 3 columns) or enter multiple if then statements to get the correct accrual rate for the years of service from "Column C". I entered the following formula to get the .0732 in Column D. =IF((C3>=7)*(C3<8),"0.0732") Is there a way to string multiples of this formula to get the correct value for Column C?

=(IF((C3>0)*(C3<1),"0.0462"))
=(IF((C3>1)*(C3<2),"0.05"))
=(IF((C3>2)*(C3<3),"0.0539"))
=(IF((C3>3)*(C3<4),"0.0577"))
=(IF((C3>4)*(C3<5),"0.0618"))
=(IF((C3>5)*(C3<6)," 0.0655"))
=(IF((C3>6)*(C3<7)," 0.0693"))
=(IF((C3>7)*(C3<8)," 0.0732"))
=(IF((C3>8)*(C3<9),". 0.077"))
=(IF((C3>9)*(C3<10)," 0.0809"))
=(IF((C3>10)*(C3<11," 0.0847"))
=(IF((C3>11)*(C3<12),"0.0885"))
=(IF((C3>12)*(C3<13),"0.0924"))
=(IF((C3>13)*(C3<14),"0.0962"))
=(IF((C3>14)*(C3<15),"0.1"))

=(IF((C3>15)*(C3<16),"0.1039"))

=(IF((C3>16),"0.1077"))

A	B	C	D	E	F	G
VAC BAL	VAC RPT THIS PAY PERIOD	YRS OF SRVC	VAC ACCRUAL RATE PER/HR	HRS WRKD THIS PAY PERIOD	VAC ACCRUED	NEW VAC BAL		YRS Begin	YRS End	Accrual Rate (per hour)
87.4800	7.50	7.474	0.0732	75	5.4900	85.4700​		0	0.99	0.0462
247.9400	3.00	7.3973		75	0.0000	244.9400​		1	1.99	0.05
5.7400	0.00	0.3014		75	0.0000	5.7400​		2	2.99	0.0539
5.5200	1.00	1.4521		75	0.0000	4.5200​		3	3.99	0.0577
11.5700	6.00	0.4959		60	0.0000	5.5700​		4	4.99	0.0616
45.9900	0.00	2.2219		75	0.0000	45.9900​		5	5.99	0.0655
32.2300	0.00	0.9233		75	0.0000	32.2300​		6	6.99	0.0693
10.2700	0.00	0.3425		75	0.0000	10.2700​		7	7.99	0.0732
2.2800	0.00	0.5726		75	0.0000	2.2800​		8	8.99	0.077
31.8500	7.50	2.2219		75	0.0000	24.3500​		9	9.99	0.0809
60.6900	2.50	2.6055		75	0.0000	58.1900​		10	10.99	0.0847
7.1200	3.50	0.9096		75	0.0000	3.6200​		11	11.99	0.0885
19.6000	4.00	8.0274		75	0.0000	15.6000​		12	12.99	0.0924
2.9300	0.00	2.2959		75	0.0000	2.9300​		13	13.99	0.0962
102.4700	0.00	4.6329		75	0.0000	102.4700​		14	14.99	0.1
105.7200	0.00	6.5315		75	0.0000	105.7200​		15	15.99	0.1039
50.6700	0.00	6.8767		75	0.0000	50.6700​		16		0.1077

 

[Fluff]

Hi & welcome to MrExcel.
How about

=INDEX($K$2:$K$18,MATCH(C2,$I$2:$I$18,1))

 

[jgharbawi]

That works beautifully!! Thank you!

 

[Fluff]

You're welcome & thanks for the feedback.