ASSIGNMENT – 31 — ANSWER KEY
Merge Table 1, 2 & 3 Using VLOOKUP
Q.1 — Employee Name of E003
Formula
=VLOOKUP("E003",'Table 1'!$A$2:$B$11,2,FALSE)Answer:
Karan
Q.2 — Department of E007
Formula
=VLOOKUP("E007",'Table 2'!$A$2:$B$11,2,FALSE)Answer:
IT
Q.3 — Salary of E005
Formula
=VLOOKUP("E005",'Table 3'!$A$2:$B$11,2,FALSE)Answer:
₹68,000
Q.4 — Employee Name & Department of E009
Employee Name
=VLOOKUP("E009",'Table 1'!$A$2:$B$11,2,FALSE)Answer: Ananya
Department
=VLOOKUP("E009",'Table 2'!$A$2:$B$11,2,FALSE)Answer: HR
Final Answer:
E009 → Ananya → HR
Q.5 — Employee Name & Salary of E002
Employee Name
=VLOOKUP("E002",'Table 1'!$A$2:$B$11,2,FALSE)Answer: Riya
Salary
=VLOOKUP("E002",'Table 3'!$A$2:$B$11,2,FALSE)Answer: ₹61,500
Final Answer:
E002 → Riya → ₹61,500
Q.6 — Department & Salary of E004
Department
=VLOOKUP("E004",'Table 2'!$A$2:$B$11,2,FALSE)Answer: HR
Salary
=VLOOKUP("E004",'Table 3'!$A$2:$B$11,2,FALSE)Answer: ₹48,500
Final Answer:
E004 → HR → ₹48,500
Q.7 — Complete Details of E006
Answer:
| EMP ID | EMP NAME | DEPARTMENT | SALARY |
|---|---|---|---|
| E006 | Simran | Finance | ₹57,500 |
Formulas
Employee Name:
=VLOOKUP("E006",'Table 1'!$A$2:$B$11,2,FALSE)Department:
=VLOOKUP("E006",'Table 2'!$A$2:$B$11,2,FALSE)Salary:
=VLOOKUP("E006",'Table 3'!$A$2:$B$11,2,FALSE)Q.8 — Complete Details of E010
Answer:
| EMP ID | EMP NAME | DEPARTMENT | SALARY |
|---|---|---|---|
| E010 | Rohit | Marketing | ₹71,000 |
Formulas
Employee Name:
=VLOOKUP("E010",'Table 1'!$A$2:$B$11,2,FALSE)Department:
=VLOOKUP("E010",'Table 2'!$A$2:$B$11,2,FALSE)Salary:
=VLOOKUP("E010",'Table 3'!$A$2:$B$11,2,FALSE)Q.9 — Complete Employee Master Table
After using VLOOKUP, the Master Table should be:
| EMP ID | EMP NAME | DEPARTMENT | SALARY |
|---|---|---|---|
| E001 | Aarav | Sales | ₹52,000 |
| E002 | Riya | Finance | ₹61,500 |
| E003 | Karan | IT | ₹75,000 |
| E004 | Neha | HR | ₹48,500 |
| E005 | Rahul | Marketing | ₹68,000 |
| E006 | Simran | Finance | ₹57,500 |
| E007 | Mohit | IT | ₹82,000 |
| E008 | Pooja | Sales | ₹55,000 |
| E009 | Ananya | HR | ₹63,500 |
| E010 | Rohit | Marketing | ₹71,000 |
Q.10 — Salary Analysis
1. Employee with Highest Salary
The highest salary is:
₹82,000
Employee:
Mohit — E007
Department:
IT
Answer:
E007 – Mohit – ₹82,000
2. Employee with Lowest Salary
The lowest salary is:
₹48,500
Employee:
Neha — E004
Department:
HR
Answer:
E004 – Neha – ₹48,500
3. Employees with Salary Above ₹70,000
| EMP ID | EMP NAME | DEPARTMENT | SALARY |
|---|---|---|---|
| E003 | Karan | IT | ₹75,000 |
| E007 | Mohit | IT | ₹82,000 |
| E010 | Rohit | Marketing | ₹71,000 |
Answer:
3 employees
Final Answer Summary
| Q. | Answer |
|---|---|
| Q.1 | E003 → Karan |
| Q.2 | E007 → IT |
| Q.3 | E005 → ₹68,000 |
| Q.4 | E009 → Ananya, HR |
| Q.5 | E002 → Riya, ₹61,500 |
| Q.6 | E004 → HR, ₹48,500 |
| Q.7 | E006 → Simran, Finance, ₹57,500 |
| Q.8 | E010 → Rohit, Marketing, ₹71,000 |
| Q.9 | Complete Master Table created |
| Q.10 | Highest = ₹82,000, Lowest = ₹48,500, Above ₹70,000 = 3 employees |
⭐ Important Concept
यह assignment दिखाता है कि VLOOKUP की मदद से अलग-अलग tables को एक common Employee ID के आधार पर merge किया जा सकता है।
EMP ID → Employee Name + Department + Salary
यही concept आगे चलकर Data Cleaning, Database Management और Excel Data Analysis में बहुत useful होता है.