ASSIGNMENT – 27

Use of Formula – VLOOKUP from One Sheet to Another Sheet

Topic: Fetching Employee Details from Sheet 1 to Sheet 2

SHEET 1 – Employee Data

Enter the following employee database in Sheet 1:

EMP ID FIRST NAME LAST NAME DEPARTMENT LOCATION
201 Aarav Sharma Finance Delhi
202 Neha Kapoor Marketing Mumbai
203 Rohan Verma IT Pune
204 Priya Singh HR Jaipur
205 Karan Mehta Finance Delhi
206 Simran Kaur Marketing Chandigarh
207 Rahul Jain IT Bengaluru
208 Ananya Gupta Finance Mumbai
209 Mohit Bansal HR Pune
210 Pooja Saini Marketing Jaipur

SHEET 2 – Use of VLOOKUP

Create the following table in Sheet 2:

EMP ID FIRST NAME LAST NAME DEPARTMENT LOCATION
203        
207        
202        
209        
205        
210        
204        
208        
201        
206        

Use VLOOKUP from Sheet 1 to fill the blank columns.

Questions

Q.1 Using VLOOKUP, find the First Name of Employee ID 203 from Sheet 1.

Q.2 Find the Last Name of Employee ID 207 using VLOOKUP.

Q.3 Find the Department of Employee ID 202.

Q.4 Find the Location of Employee ID 209.

Q.5 Find the First Name, Last Name and Department of Employee ID 205.

Q.6 Find the complete details of Employee ID 210 using VLOOKUP.

Q.7 Find the Department and Location of Employee ID 204.

Q.8 Find the First Name and Location of Employee ID 208.

Q.9 Find the complete details of Employee ID 201.

Q.10 Fill the complete Sheet 2 using VLOOKUP from Sheet 1.

Formula Examples

Assume:

  • Sheet 2 Employee ID is in A2

  • Sheet 1 data is in A2:E11

First Name

=VLOOKUP(A2,'Sheet 1'!$A$2:$E$11,2,FALSE)

Last Name

=VLOOKUP(A2,'Sheet 1'!$A$2:$E$11,3,FALSE)

Department

=VLOOKUP(A2,'Sheet 1'!$A$2:$E$11,4,FALSE)

Location

=VLOOKUP(A2,'Sheet 1'!$A$2:$E$11,5,FALSE)

How to Complete Sheet 2

If the columns in Sheet 2 are:

Column Information
A EMP ID
B FIRST NAME
C LAST NAME
D DEPARTMENT
E LOCATION

Enter the following formulas in Row 2:

B2:

=VLOOKUP(A2,'Sheet 1'!$A$2:$E$11,2,FALSE)

C2:

=VLOOKUP(A2,'Sheet 1'!$A$2:$E$11,3,FALSE)

D2:

=VLOOKUP(A2,'Sheet 1'!$A$2:$E$11,4,FALSE)

E2:

=VLOOKUP(A2,'Sheet 1'!$A$2:$E$11,5,FALSE)

Then drag all four formulas down to complete Sheet 2.

Important Points

  • VLOOKUP searches for the Employee ID in the first column of the selected table.

  • 'Sheet 1'! tells Excel to get the data from Sheet 1.

  • $A$2:$E$11 keeps the lookup table fixed while copying the formula down.

  • FALSE means Exact Match.

Skills Covered

Students will learn:

  • VLOOKUP between two worksheets

  • Using a worksheet reference in a formula

  • Exact Match using FALSE

  • Using absolute references $

  • Fetching multiple columns using VLOOKUP

  • Creating an automated lookup table

  • Filling a complete report from a master database

PreviousNext