Lookup Functions

The purpose of this project is to practice and apply what you have learned about using the VLookup and HLookup in Excel. In this project, you will use and create spreadsheets that require VLookup or HLookup to complete several tasks. Download the Project 5 Excel file to use for this project.

Part 1

Requirements:

• Download and open the P5-Data.xlsx workbook.
• Open the Part1 spreadsheet. This spreadsheet includes a information about a list of employees.
• Use the VLookup function to populate the table in columns H-L. You need to look up the email, department, and supervisor last name and supervisor first name for the five employees identified in column H.
  • The employee ID numbers are already provided for you in column H.
  • You may add any additional columns or information you need to complete the task.
  • You must use VLookup to populate all information in the new table except for the employee IDs in column H.

Part 2

Requirements:

• Go to the Part2 spreadsheet in the same workbook. This spreadsheet shows a list of the most popular surnames in the United States in the year 2000. It also includes other columns of information with various statistics about the surnames. Columns G-L show the percentage of the racial makeup of those who have that surname.
• Choose five random surnames from the list of surnames and enter those five names in cells N2-N6.
• Use the VLookup function to show the Black, Asian, and Hispanic makeup of those who have those last names for each of the last names.
  • You may add any additional columns or information you need to complete the task.
  • You must use VLookup to populate all information in the new table except for the surnames in column N.

Part 3

Requirements:

• Go to the Part3 spreadsheet in the same workbook. This spreadsheet shows only the Sales persons from the list of employees in Part 1. However, it also adds the sales amount they achieved during the month. Additionally, the spreadsheet also adds columns for Commission Rate and Pay for the sales persons.
• Create a horizontal (rows) commission table to use to calculate the commission rate and pay for each of the sales persons listed in the spreadsheet. You will then need to use HLookup to look up the information in this commission table to calculate the pay for the employees.
  • The commission table must be horizontal.
  • The commission table should include the following rates: <$50,000 = 5% commission rate, $50,000-$64,999 = 10% commission rate, $65,000-79,999 = 12% commission rate, $80,000-$99,999 = 15% commission rate, $100,000-$124,999 = 20% commission rate, >$125,000 = 25% commission rate.
• Using HLookup and the commission rate table you just created, calculate the commission rate in column H for each of the sales persons. (Linda should receive 12% commission, Mary should receive 20% commission, and Jennifer should receive 5% commission.) Use a formula in column I to multiply the sales amount in column G by the commission rate in column H for each of the employees. This should give you the pay amount for each.
  • Format the Pay to be in currency with two decimal places.

When you are finished with the three parts, submit your Excel workbook with all three spreadsheets to Blackboard.