ASSIGNMENT – 31

Merge Table 1, 2 & 3 Using VLOOKUP

Topic: Combining Data from Multiple Tables Using VLOOKUP

In this assignment, you have three separate tables containing:

  • Employee Details

  • Department

  • Salary

Your task is to merge all three tables into one complete Employee Master Table using VLOOKUP.

TABLE 1 – Employee Details

EMP ID EMP NAME
E001 Aarav
E002 Riya
E003 Karan
E004 Neha
E005 Rahul
E006 Simran
E007 Mohit
E008 Pooja
E009 Ananya
E010 Rohit

TABLE 2 – Department

EMP ID DEPARTMENT
E001 Sales
E002 Finance
E003 IT
E004 HR
E005 Marketing
E006 Finance
E007 IT
E008 Sales
E009 HR
E010 Marketing

TABLE 3 – Salary

EMP ID SALARY
E001 ₹52,000
E002 ₹61,500
E003 ₹75,000
E004 ₹48,500
E005 ₹68,000
E006 ₹57,500
E007 ₹82,000
E008 ₹55,000
E009 ₹63,500
E010 ₹71,000

TASK – Create Employee Master Table

Create a new table called Employee Master Table.

The table should contain:

EMP ID EMP NAME DEPARTMENT SALARY
E001      
E002      
E003      
E004      
E005      
E006      
E007      
E008      
E009      
E010      

Use VLOOKUP to fill the blank columns.

Questions

Q.1 Using VLOOKUP, find the Employee Name of Employee ID E003.

Q.2 Using VLOOKUP, find the Department of Employee ID E007.

Q.3 Using VLOOKUP, find the Salary of Employee ID E005.

Q.4 Find the Employee Name and Department of Employee ID E009 using VLOOKUP.

Q.5 Find the Employee Name and Salary of Employee ID E002.

Q.6 Find the Department and Salary of Employee ID E004.

Q.7 Find the complete details of Employee ID E006.

Q.8 Find the complete details of Employee ID E010.

Q.9 Using VLOOKUP, complete the Employee Master Table for all 10 employees.

Q.10 After completing the Master Table, identify:

  1. Employee with the highest salary

  2. Employee with the lowest salary

  3. Employee with salary above ₹70,000

Formula Examples

Assume the Master Table starts from A1.

Employee Name

If A2 contains the Employee ID:

=VLOOKUP(A2,$A$2:$B$11,2,FALSE)

However, if the original Employee Details table is on Sheet1, use:

=VLOOKUP(A2,'Table 1'!$A$2:$B$11,2,FALSE)

Department from Table 2

If Table 2 is in columns A:B:

=VLOOKUP(A2,'Table 2'!$A$2:$B$11,2,FALSE)

Salary from Table 3

If Table 3 is in columns A:B:

=VLOOKUP(A2,'Table 3'!$A$2:$B$11,2,FALSE)

Complete Master Table Formula Setup

Suppose:

  • A2 = Employee ID

  • B2 = Employee Name

  • C2 = Department

  • D2 = Salary

B2 – Employee Name

=VLOOKUP(A2,'Table 1'!$A$2:$B$11,2,FALSE)

C2 – Department

=VLOOKUP(A2,'Table 2'!$A$2:$B$11,2,FALSE)

D2 – Salary

=VLOOKUP(A2,'Table 3'!$A$2:$B$11,2,FALSE)

Then drag all formulas down to Row 11.

Important Learning Point

All three tables have a common field: EMP ID.

This common field is called the Lookup Value / Key.

For example:

E007

can be used to find:

  • Employee Name → Mohit

  • Department → IT

  • Salary → ₹82,000

So VLOOKUP allows us to combine information from different tables into one Master Table.

Skills Covered

Students will learn:

  • VLOOKUP with multiple tables

  • Merging data using a common ID

  • Creating an Employee Master Table

  • Using different table ranges

  • Using FALSE for Exact Match

  • Using absolute references $

  • Fetching text and numeric data

  • Building a practical database-style Excel report

PreviousNext