ASSIGNMENT – 10
Answer Key
Employee Database – Completed VLOOKUP Table
| Employee ID | Full Name | SSN | Department | Start Date | Earnings |
|---|---|---|---|---|---|
| EMP001 | Aarav Sharma | 421-56-7832 | Marketing | 15-01-2020 | ₹72,000 |
| EMP002 | Meera Kapoor | 583-21-6490 | Finance | 20-02-2020 | ₹85,000 |
| EMP003 | Rohan Verma | 317-84-5261 | IT/IS | 12-03-2020 | ₹95,000 |
| EMP004 | Ananya Singh | 692-35-4178 | Marketing | 05-04-2020 | ₹1,10,000 |
| EMP005 | Kunal Mehta | 245-73-8619 | Engineering | 18-05-2020 | ₹92,000 |
Q.1 How many employees are there in the list?
Formula:
=COUNTA(A2:A15)Answer: 14 Employees
Q.2 How many employees work in Finance, Marketing and Engineering?
Finance
=COUNTIF(D2:D15,"Finance")Answer: 3
Marketing
=COUNTIF(D2:D15,"Marketing")Answer: 5
Engineering
=COUNTIF(D2:D15,"Engineering")Answer: 3
Final Answer
| Department | Number of Employees |
|---|---|
| Finance | 3 |
| Marketing | 5 |
| Engineering | 3 |
Q.3 Find the Department and Earnings of Kunal Mehta.
Department
=VLOOKUP("Kunal Mehta",B2:F15,3,FALSE)Answer: Engineering
Earnings
=VLOOKUP("Kunal Mehta",B2:F15,5,FALSE)Answer: ₹92,000
Q.4 Find the SSN of Kunal Mehta.
=VLOOKUP("Kunal Mehta",B2:F15,2,FALSE)Answer: 245-73-8619
Q.5 Find the total Earnings of employees working in Marketing.
=SUMIF(D2:D15,"Marketing",F2:F15)Marketing employees:
₹72,000 + ₹1,10,000 + ₹88,000 + ₹98,000 + ₹82,000
Answer: ₹4,50,000
Q.6 Find the Full Name and Department of Employee ID EMP010.
Full Name
=VLOOKUP("EMP010",A2:F15,2,FALSE)Answer: Simran Kaur
Department
=VLOOKUP("EMP010",A2:F15,4,FALSE)Answer: IT/IS
Q.7 Find the Earnings of Employee ID EMP013.
=VLOOKUP("EMP013",A2:F15,6,FALSE)Answer: ₹1,32,000
Q.8 How many employees have Earnings greater than ₹1,00,000?
=COUNTIF(F2:F15,">100000")Employees earning more than ₹1,00,000:
-
EMP004 – ₹1,10,000
-
EMP007 – ₹1,05,000
-
EMP010 – ₹1,20,000
-
EMP012 – ₹1,15,000
-
EMP013 – ₹1,32,000
Answer: 5 Employees
Q.9 Find the total Earnings of employees working in Finance.
=SUMIF(D2:D15,"Finance",F2:F15)Finance employees:
₹85,000 + ₹68,000 + ₹76,000
Answer: ₹2,29,000
Q.10 Find the Full Name, Department, Start Date and Earnings of EMP004, EMP008 and EMP012.
EMP004
=VLOOKUP("EMP004",A2:F15,2,FALSE)Full Name: Ananya Singh
=VLOOKUP("EMP004",A2:F15,4,FALSE)Department: Marketing
=VLOOKUP("EMP004",A2:F15,5,FALSE)Start Date: 05-04-2020
=VLOOKUP("EMP004",A2:F15,6,FALSE)Earnings: ₹1,10,000
EMP008
=VLOOKUP("EMP008",A2:F15,2,FALSE)Full Name: Neha Bansal
=VLOOKUP("EMP008",A2:F15,4,FALSE)Department: Marketing
=VLOOKUP("EMP008",A2:F15,5,FALSE)Start Date: 14-08-2020
=VLOOKUP("EMP008",A2:F15,6,FALSE)Earnings: ₹88,000
EMP012
=VLOOKUP("EMP012",A2:F15,2,FALSE)Full Name: Pooja Saini
=VLOOKUP("EMP012",A2:F15,4,FALSE)Department: Human Resources
=VLOOKUP("EMP012",A2:F15,5,FALSE)Start Date: 08-12-2020
=VLOOKUP("EMP012",A2:F15,6,FALSE)Earnings: ₹1,15,000
Final Answer Summary
| Question | Answer |
|---|---|
| Q1 | 14 Employees |
| Q2 – Finance | 3 |
| Q2 – Marketing | 5 |
| Q2 – Engineering | 3 |
| Q3 – Kunal Department | Engineering |
| Q3 – Kunal Earnings | ₹92,000 |
| Q4 – Kunal SSN | 245-73-8619 |
| Q5 – Marketing Total Earnings | ₹4,50,000 |
| Q6 – EMP010 Name | Simran Kaur |
| Q6 – EMP010 Department | IT/IS |
| Q7 – EMP013 Earnings | ₹1,32,000 |
| Q8 – Earnings > ₹1,00,000 | 5 Employees |
| Q9 – Finance Total Earnings | ₹2,29,000 |
Q10 Summary
| Employee ID | Full Name | Department | Start Date | Earnings |
|---|---|---|---|---|
| EMP004 | Ananya Singh | Marketing | 05-04-2020 | ₹1,10,000 |
| EMP008 | Neha Bansal | Marketing | 14-08-2020 | ₹88,000 |
| EMP012 | Pooja Saini | Human Resources | 08-12-2020 | ₹1,15,000 |