Lab 04 Using VLOOKUP and Calculation Functions
Data Setup
Sheet 1: Employee Performance
This sheet will contain anonymized performance data with the following
columns:
• EmployeeID
• Project Code
• Performance Score
Example data for Sheet 1:
EmployeeID Project Code Performance Score
2001 P1 85
2002 P2 78
2003 P3 92
2004 P1 88
2005 P2 75
Sheet 2: Employee Details
This sheet will list employee IDs and their corresponding names and
departments:
• EmployeeID
• First Name
• Last Name
• Department
Example data for Sheet 2:
EmployeeID First Name Last Name Department
2001 John Doe Marketing
2002 Jane Smith Sales
2003 Emily Johnson IT
2004 Michael Brown HR
2005 Sarah Davis Finance
Exercise Tasks
1. VLOOKUP Function: Use the VLOOKUP function on Sheet 1 to find and
display the last names of the employees next to their performance scores
using the data from Sheet 2.
2. Named Range: Create a named range for the employee details on Sheet 2
and use it in the VLOOKUP function.
3. Calculate Average Performance: Calculate the average performance
score for all employees using the AVERAGE function.
4. Highlight Top Performers: Use conditional formatting to highlight the
rows of employees in Sheet 1 who have a performance score above 90.
5. Sort by Department: Sort the data in Sheet 2 by the department in
alphabetical order.
6. Freeze Header Rows: Freeze the header rows on both sheets for easier
navigation.
End Of Lab 04
