The first sheet is for payroll and the second sheet is called data. I am trying to make it so that if the job level is 7-9 then they do not get paid overtime pay included in the gross. The equation for OT rate is 1.5 x Hourly Rate & Gross pay = HourlyRate*RegularHrs +HourlyRate* OTHours*1.5. What would be the correct formula, can I use vlookup and an if function?

uhan one me. (2 ponit 9. Employees with Job Levels of 7 or greater are not paid overtime. Instead their overtime hours are recorded and then converted to time off (lieu days). You must make sure employees at Job Level 7 or greater are not paid overtime. (4 point deduction if not done) 10 All cell entries (1abels and numbers) must be visible No hidden letters. no. fix =((VLOOKUP(B5,data!$B$5:$C$13,2)*C5)+((VLOOKUP(B5,data!$B$5:$C$13,2)*D5*1.5)) E5 A C F K L 2 For the month 2016 October XYZ Corporation Payroll Medical Life Ins 4 EmployeelD Job Level Regular Hrs OT Hours Income Tax Gross CPP El RRSP Union Net $1,060.00 $481.19 $47.70 $19.08 $180.20 $9.00 $15.90 $4.50 $783.62 1000240 7 40 $783.75 $80.19 $35.27 $14.11 $133.24 $6.00 $11.76 $3.00 $580.38 1000380 2 40 5 $880.00 $320.77 $39.60 $15.84 $149.60 $8.00 $13.20 $4.00 $649.76 1000406 40 5 $996.00 $401.00 $44.82 $17.93 $169.32 $8.00 $14.94 $4.00 $736.99 1000557 40 1 $720.00 $160.38 $32.40 $12.96 $122.40 $6.00 $10.80 $3.00 $532.44 40 1000629 3 $830.00 $246.77 $37.35 $14.94 $141.10 $8.00 $12.45 $4.00 $612.16 10 1000755 40 $960.00 $561.38 $43.20 $17.28 $163.20 $12.00 $14.40 $6.00 $703.92 11 32 1001156 8 $1,068.00 $401.00 $48.06 $19.22 $181.56 $8.00 $16.02 $4.00 $791.14 40 1001252 6 $403.00 $0.00 $18.14 $7.25 $68.51 $6.00 $6.05 $3.00 $294.06 26 1001292 1 $801.00 $160.38 $36.05 $14.42 $136.17 $6.00 $12.02 $3.00 $593.35 40 1001297 3 $1,060.00 $481.19 $47.70 $19.08 $180.20 $9.00 $15.90 $4.50 $783.62 40 1001307 7 $14.40 $800.00 $246.77 $36.00 $136.00 $8.00 $12.00 $4.00 $589.60 16 40 1001310 D E F G Salary and Deduction information 1 2 Income Tax Deductions Weekly Life Weekly Annual Job Level Hourly Rate Medical Deduction Insurance Income $0.00 Income $15.50 $6.00 $3.00 $0.00 $0.00 5 1 $16.50 $6.00 $3.00 $200.00 $ 6 10,400.00 $ 2 40.10 $400.00 $ 20,800.00 $ $600.00 $ 31,200.00 $ $800.00 $ 41,600.00 $ $18.00 $6.00 $3.00 7 3 80.19 $20.00 $8.00 $4.00 4 123.38 $22.00 $8.00 $4.00 5 160.38 $24.00 $8.00 $4.00 $1,000.00 $ 52,000.00 $ $ 1,200.00 $ $1,400.00 $ $ 1,600.00 $ 83,200.00 $ $ 1,800.00 $ 93,600.00 $ 10 6 200.50 $26.50 $9.00 $4.50 11 62,400.00 $ 7 240.60 $30.00 $12.00 $6.00 12 72,800.00 $ 280.69 $40.00 $16.00 $8.00 13 320.79 14 457.29 15 16 17 18 19 20 21 22 23 24 25 26 docs ayroll data LO uhan one me. (2 ponit 9. Employees with Job Levels of 7 or greater are not paid overtime. Instead their overtime hours are recorded and then converted to time off (lieu days). You must make sure employees at Job Level 7 or greater are not paid overtime. (4 point deduction if not done) 10 All cell entries (1abels and numbers) must be visible No hidden letters. no. fix =((VLOOKUP(B5,data!$B$5:$C$13,2)*C5)+((VLOOKUP(B5,data!$B$5:$C$13,2)*D5*1.5)) E5 A C F K L 2 For the month 2016 October XYZ Corporation Payroll Medical Life Ins 4 EmployeelD Job Level Regular Hrs OT Hours Income Tax Gross CPP El RRSP Union Net $1,060.00 $481.19 $47.70 $19.08 $180.20 $9.00 $15.90 $4.50 $783.62 1000240 7 40 $783.75 $80.19 $35.27 $14.11 $133.24 $6.00 $11.76 $3.00 $580.38 1000380 2 40 5 $880.00 $320.77 $39.60 $15.84 $149.60 $8.00 $13.20 $4.00 $649.76 1000406 40 5 $996.00 $401.00 $44.82 $17.93 $169.32 $8.00 $14.94 $4.00 $736.99 1000557 40 1 $720.00 $160.38 $32.40 $12.96 $122.40 $6.00 $10.80 $3.00 $532.44 40 1000629 3 $830.00 $246.77 $37.35 $14.94 $141.10 $8.00 $12.45 $4.00 $612.16 10 1000755 40 $960.00 $561.38 $43.20 $17.28 $163.20 $12.00 $14.40 $6.00 $703.92 11 32 1001156 8 $1,068.00 $401.00 $48.06 $19.22 $181.56 $8.00 $16.02 $4.00 $791.14 40 1001252 6 $403.00 $0.00 $18.14 $7.25 $68.51 $6.00 $6.05 $3.00 $294.06 26 1001292 1 $801.00 $160.38 $36.05 $14.42 $136.17 $6.00 $12.02 $3.00 $593.35 40 1001297 3 $1,060.00 $481.19 $47.70 $19.08 $180.20 $9.00 $15.90 $4.50 $783.62 40 1001307 7 $14.40 $800.00 $246.77 $36.00 $136.00 $8.00 $12.00 $4.00 $589.60 16 40 1001310 D E F G Salary and Deduction information 1 2 Income Tax Deductions Weekly Life Weekly Annual Job Level Hourly Rate Medical Deduction Insurance Income $0.00 Income $15.50 $6.00 $3.00 $0.00 $0.00 5 1 $16.50 $6.00 $3.00 $200.00 $ 6 10,400.00 $ 2 40.10 $400.00 $ 20,800.00 $ $600.00 $ 31,200.00 $ $800.00 $ 41,600.00 $ $18.00 $6.00 $3.00 7 3 80.19 $20.00 $8.00 $4.00 4 123.38 $22.00 $8.00 $4.00 5 160.38 $24.00 $8.00 $4.00 $1,000.00 $ 52,000.00 $ $ 1,200.00 $ $1,400.00 $ $ 1,600.00 $ 83,200.00 $ $ 1,800.00 $ 93,600.00 $ 10 6 200.50 $26.50 $9.00 $4.50 11 62,400.00 $ 7 240.60 $30.00 $12.00 $6.00 12 72,800.00 $ 280.69 $40.00 $16.00 $8.00 13 320.79 14 457.29 15 16 17 18 19 20 21 22 23 24 25 26 docs ayroll data LO