ASSIGNMENT – 27 — ANSWER KEY
VLOOKUP from One Sheet to Another Sheet
Sheet 1 Data Range
'Sheet 1'!A2:E11Q.1 — First Name of Employee ID 203
Formula
=VLOOKUP(A2,'Sheet 1'!$A$2:$E$11,2,FALSE)Answer:
Rohan
Q.2 — Last Name of Employee ID 207
Formula
=VLOOKUP(A3,'Sheet 1'!$A$2:$E$11,3,FALSE)Answer:
Jain
Q.3 — Department of Employee ID 202
Formula
=VLOOKUP(A4,'Sheet 1'!$A$2:$E$11,4,FALSE)Answer:
Marketing
Q.4 — Location of Employee ID 209
Formula
=VLOOKUP(A5,'Sheet 1'!$A$2:$E$11,5,FALSE)Answer:
Pune
Q.5 — First Name, Last Name & Department of Employee ID 205
Formula — First Name
=VLOOKUP(A6,'Sheet 1'!$A$2:$E$11,2,FALSE)Formula — Last Name
=VLOOKUP(A6,'Sheet 1'!$A$2:$E$11,3,FALSE)Formula — Department
=VLOOKUP(A6,'Sheet 1'!$A$2:$E$11,4,FALSE)Answer:
Karan Mehta — Finance
Q.6 — Complete Details of Employee ID 210
Formula
First Name:
=VLOOKUP(A7,'Sheet 1'!$A$2:$E$11,2,FALSE)Last Name:
=VLOOKUP(A7,'Sheet 1'!$A$2:$E$11,3,FALSE)Department:
=VLOOKUP(A7,'Sheet 1'!$A$2:$E$11,4,FALSE)Location:
=VLOOKUP(A7,'Sheet 1'!$A$2:$E$11,5,FALSE)Answer:
| EMP ID | First Name | Last Name | Department | Location |
|---|---|---|---|---|
| 210 | Pooja | Saini | Marketing | Jaipur |
Q.7 — Department & Location of Employee ID 204
Answer:
Department: HR
Location: Jaipur
Formulas
=VLOOKUP(A8,'Sheet 1'!$A$2:$E$11,4,FALSE)=VLOOKUP(A8,'Sheet 1'!$A$2:$E$11,5,FALSE)Q.8 — First Name & Location of Employee ID 208
Answer:
First Name: Ananya
Location: Mumbai
Formulas
=VLOOKUP(A9,'Sheet 1'!$A$2:$E$11,2,FALSE)=VLOOKUP(A9,'Sheet 1'!$A$2:$E$11,5,FALSE)Q.9 — Complete Details of Employee ID 201
Answer:
| EMP ID | First Name | Last Name | Department | Location |
|---|---|---|---|---|
| 201 | Aarav | Sharma | Finance | Delhi |
Formulas
=VLOOKUP(A10,'Sheet 1'!$A$2:$E$11,2,FALSE)=VLOOKUP(A10,'Sheet 1'!$A$2:$E$11,3,FALSE)=VLOOKUP(A10,'Sheet 1'!$A$2:$E$11,4,FALSE)=VLOOKUP(A10,'Sheet 1'!$A$2:$E$11,5,FALSE)Q.10 — Complete Sheet 2
After applying VLOOKUP, Sheet 2 will look like this:
| EMP ID | FIRST NAME | LAST NAME | DEPARTMENT | LOCATION |
|---|---|---|---|---|
| 203 | Rohan | Verma | IT | Pune |
| 207 | Rahul | Jain | IT | Bengaluru |
| 202 | Neha | Kapoor | Marketing | Mumbai |
| 209 | Mohit | Bansal | HR | Pune |
| 205 | Karan | Mehta | Finance | Delhi |
| 210 | Pooja | Saini | Marketing | Jaipur |
| 204 | Priya | Singh | HR | Jaipur |
| 208 | Ananya | Gupta | Finance | Mumbai |
| 201 | Aarav | Sharma | Finance | Delhi |
| 206 | Simran | Kaur | Marketing | Chandigarh |
Final Answer Summary
| Q. | EMP ID | Answer |
|---|---|---|
| Q.1 | 203 | Rohan |
| Q.2 | 207 | Jain |
| Q.3 | 202 | Marketing |
| Q.4 | 209 | Pune |
| Q.5 | 205 | Karan Mehta – Finance |
| Q.6 | 210 | Pooja Saini – Marketing – Jaipur |
| Q.7 | 204 | HR – Jaipur |
| Q.8 | 208 | Ananya – Mumbai |
| Q.9 | 201 | Aarav Sharma – Finance – Delhi |
| Q.10 | All IDs | Complete Sheet 2 filled using VLOOKUP |
⭐ Important Formula Concept
The column number in VLOOKUP is based on Sheet 1’s selected table:
| Column | Data | Column Number |
|---|---|---|
| A | EMP ID | 1 |
| B | FIRST NAME | 2 |
| C | LAST NAME | 3 |
| D | DEPARTMENT | 4 |
| E | LOCATION | 5 |
So:
=VLOOKUP(A2,'Sheet 1'!$A$2:$E$11,2,FALSE)means:
A2 की Employee ID को Sheet 1 में खोजो → 2nd column से First Name दो → Exact Match करो.