Instructions:
1. For cell D16 you need to use Vlookup it is based on the job title of each employee. (3 columns in the table below)
2. Naming a range: the yellow cells in the table below-cells B31:D36 should be named "Rate"
3. Use the range named Rate in your vlookup D16 and copy the vlookup till cell D23
4. Use an IF calculations for over 40 hours, time and half pay in E16. Copy your if down till cell E23
5. For column F and G. You should refer to the cell F14 in the formula and G14 as well when you calculate the taxes. Use the fixed refrence-cell address ($F$1 for example) to refer to the 20% and the 7.65%
6. Finish all the calculation (hint: Gross-taxes=Net pay). Auto sum for cell e24 till H24
Lexington Hospital for Special Surgery
Payroll Summary
For the Week Ending November 16, 2020
					FED INC	SOC SEC
EMPLOYEE		HOURS	HOURLY	GROSS	TAX WITH	TAX WITH	NET
NAME	JOB TITLE	WORKED	RATE	PAY	20%	7.65%	PAY
Barnes	Trainee	15	$ 11.00	$ 165.00	-33%	-13%	$ 119.00
Chen	Technician Level 2	35	$ 31.00	$ 1,085.00	-217%	-83%	$ 785.00
Clifford	Technician Level 1	35	$ 22.00	$ 770.00	-154%	-59%	$ 557.00
Gold	Floor Assistant	25	$ 18.00	$ 450.00	-90%	-34%	$ 326.00
Mangano	Trainee	15	$ 11.00	$ 165.00	-33%	-13%	$ 119.00
Murphy	Technician Level 2	50	$ 31.00	$ 1,395.00	-279%	-107%	$ 1,009.00
Rashad	Laboratory Specialist	40	$ 38.50	$ 1,540.00	-308%	-118%	$ 1,114.00
Ruiz	Technician Level 1	45	$ 22.00	$ 935.00	-187%	-72%	$ 676.00
Totals		260	$ 184.50	$ 6,505.00	-1301%	-499%	$ 4,705.00
	Employee Rate Schedule
			Hourly
	Job Title		Rate
	Floor Assistant		18.00
	Laboratory Specialist		38.50
	Technician Level 1		22.00
	Technician Level 2		31.00
	Technician Level 3		40.00
	Trainee		11.00

Please show formulas clearly.