Name the range N$21$:P$31$ "Name Lookup"

Use VLOOKUP formulas to find an exact match for the First and Last names of the employees based on their employee numbers. $($Hint: use the range name or be sure to lock cells as needed$)$

In column K$,$ calculate the Annual compensation as Annual Salary plus Bonus plus Benefits.

In cell G$37$ use the SUMIF formula to total any Annual Compensation greater than $$75,000.$

Insert a table for the data below.

Add a total row to the table. Sum columns $G-K$ and count column $C$ in the Total Row.

Format column G $-$ K as currency with no decimals.

Sort the table on Last Name $($column D$).$

Change the table style to one you like.

In cell L$22,$ create an IF formula that puts $"$NYC$"$ in column $L$ if column $F$ is "New York", if not put "Other." Copy this formula to the remaining rows in column $L.$

Filter the table for any employee hired between $\frac{1}{1}\frac{?}{2013}$ and $\frac{12}{31}\frac{?}{2013}.$ In Cell G$40,$ type $($do not use a formula$)$ the results for count of employees.

Clear the filter.

Filter the table for any employee with a last name that begins with M$.$ In cell G$42,$ type $($do not use a formula$)$ the results for the total Annual Compensation. Clear the filter.

Insert a comment in cell H$26$ that includes your name and "This salary seems high."

Rename the tab $($sheet name$)$ to Payroll Data and change the tab color to Blue

Enter your name as a center header, save the file, and submit your file$\mathrm{.}2)$ Use VLOOKUP formulas to find an exact match for the First and Last names of the employees based on their employee numbers. $($Hint: use the range name or be sure to lock cells as needed$)$

$3)$ In column K$,$ calculate the Annual compensation as Annual Salary plus Bonus plus Benefits.

$4)$ In cell G$37$ use the SUMIF formula to total any Annual Compensation greater than $$75,000.$

$5)$ Insert a table for the data below.

$6)$ Add a total row to the table. Sum columns G $-$ K and count column C in the Total Row.

$7)$ Format columns G $-$ K as currency with no decimals.

$8)$ Sort the table on Last Name $($column D$).$

$9)$ Change the table style to one you like.

$10)$ In cell L$22,$ create an IF formula that puts $"$NYC$"$ in column L if column F is "New York", if not put "Other." Copy this formula to the remaining rows in column L$.$

$11)$ Filter the table for any employee hired between $1/1/2013$ and $12/31/2013.$ In Cell G$40,$ type $($do not use a formula$)$ the results for count of employees.

$12)$ Clear the filter.

$13)$ Filter the table for any employee with a last name that begins with M$.$ In cell G$42,$ type $($do not use a formula$)$ the results for the total Annual Compensation. Clear the filter.

$14)$ Insert a comment in cell H$26$ that includes your name and "This salary seems high."

$15)$ Rename the tab $($sheet name$)$ to Payroll Data and change the tab color to Blue

$16)$ Enter your name as a center header, save the file, and submit your file.

Name Lookup

"Employee

Number" "First

Name" "Last

Name" Hire Date Branch "Monthly

Salary" "Annual

Salary" "Annual

Bonus" "Benefits

Dollars" "Annual

Compensation" "Employee

Number" "First

Name" "Last

Name"

$12112/12/2013$ New York $465055800137012834700041211$ Mary Lane

$45154/1/2014$ Boston $597071640572516477\mathrm{.}293842\mathrm{.}22120$ Ellen Meeb

$41875/6/2013$ Philadelphia $7550906006525208381179632213$ Jake Gray

$259912/10/2013$ Boston $84501014007550233221322722599$ Martha Macky

$22132/15/2015$ Boston $2950354006808142442223955$ Jen Richards

$65883/25/2013$ New York $2750330006307590412204187$ Debbie Davie

$21206/23/2012$ New York $422550700132011661636814450$ Mark Mollen

$44508/3/2015$ Philadelphia $6750810005900186301055304515$ Leanne Waters

$98879/29/2014$ Philadelphia $85001020007650234601331106588$ Paul Early

$39555/12/2013$ Boston $37504500079510350561459887$ Hank Randal

$104943\mathrm{.}84$

Compensation $>75,000582717\mathrm{.}2$

Count of employees hired between $1/1/2013$ and $12/31/13$

Total Compensation for employee with a last name that begins with M