ASSIGNMENT – 9 – ANSWER KEY
LOOKUP Function
LOOKUP Syntax
=LOOKUP(lookup_value, lookup_vector, result_vector)Table 1 – Completed Employee Details
| Employee ID | Last Name | First Name |
|---|---|---|
| 120145 | Sharma | Amit |
| 235078 | Verma | Neha |
| 348216 | Singh | Rahul |
| 451092 | Kapoor | Priya |
| 562340 | Mehta | Rohan |
| 673125 | Gupta | Anjali |
| 784236 | Khan | Arjun |
| 895147 | Joshi | Pooja |
| 906258 | Malhotra | Vivek |
| 117369 | Saini | Kiran |
| 228470 | Bansal | Mohit |
| 339581 | Yadav | Simran |
Table 2 – Completed Employee Salary
| Employee ID | Pay | First Name | Last Name |
|---|---|---|---|
| 784236 | ₹92,500 | Arjun | Khan |
| 451092 | ₹1,28,600 | Priya | Kapoor |
| 117369 | ₹1,45,750 | Kiran | Saini |
| 235078 | ₹88,900 | Neha | Verma |
| 562340 | ₹1,16,450 | Rohan | Mehta |
| 339581 | ₹76,800 | Simran | Yadav |
| 906258 | ₹1,32,250 | Vivek | Malhotra |
| 348216 | ₹97,500 | Rahul | Singh |
| 673125 | ₹1,54,300 | Anjali | Gupta |
| 120145 | ₹1,10,750 | Amit | Sharma |
| 895147 | ₹82,600 | Pooja | Joshi |
| 228470 | ₹1,25,900 | Mohit | Bansal |
Q.1 LOOKUP – Employee ID 784236
Formula
=LOOKUP(784236,A2:A13,C2:C13)Answer:
ARJUN
Q.2 LOOKUP – Employee ID 451092
Formula
=LOOKUP(451092,A2:A13,B2:B13)Answer:
KAPOOR
Q.3 Employee ID 117369
First Name
=LOOKUP(117369,A2:A13,C2:C13)Answer: KIRAN
Last Name
=LOOKUP(117369,A2:A13,B2:B13)Answer: SAINI
Final Answer:
KIRAN SAINI
Q.4 Employee ID 562340
First Name
=LOOKUP(562340,A2:A13,C2:C13)Answer: ROHAN
Last Name
=LOOKUP(562340,A2:A13,B2:B13)Answer: MEHTA
Final Answer:
ROHAN MEHTA
Q.5 Employee ID 673125
First Name
=LOOKUP(673125,A2:A13,C2:C13)Answer: ANJALI
Last Name
=LOOKUP(673125,A2:A13,B2:B13)Answer: GUPTA
Final Answer:
ANJALI GUPTA
Q.6 Employee ID 906258
First Name
=LOOKUP(906258,A2:A13,C2:C13)Answer: VIVEK
Last Name
=LOOKUP(906258,A2:A13,B2:B13)Answer: MALHOTRA
Final Answer:
VIVEK MALHOTRA
Q.7 Employee ID 339581
First Name
=LOOKUP(339581,A2:A13,C2:C13)Answer: SIMRAN
Last Name
=LOOKUP(339581,A2:A13,B2:B13)Answer: YADAV
Final Answer:
SIMRAN YADAV
Q.8 Employee ID 228470
First Name
=LOOKUP(228470,A2:A13,C2:C13)Answer: MOHIT
Last Name
=LOOKUP(228470,A2:A13,B2:B13)Answer: BANSAL
Final Answer:
MOHIT BANSAL
Q.9 LOOKUP – RAHUL
From Table 1:
RAHUL = Employee ID 348216
From Table 2:
Employee ID 348216 = ₹97,500
Employee ID
=LOOKUP("RAHUL",C2:C13,A2:A13)Answer: 348216
Pay
If the salary table has First Name in column C:
=LOOKUP("RAHUL",C2:C13,B2:B13)Answer: ₹97,500
Final Answer:
Employee ID = 348216
Pay = ₹97,500
Q.10 LOOKUP – POOJA
From Table 1:
POOJA = Employee ID 895147
From Table 2:
Employee ID 895147 = ₹82,600
Employee ID
=LOOKUP("POOJA",C2:C13,A2:A13)Answer: 895147
Pay
=LOOKUP("POOJA",C2:C13,B2:B13)Answer: ₹82,600
Final Answer:
Employee ID = 895147
Pay = ₹82,600
Final Answers – Quick Reference
| Q. No. | Answer |
|---|---|
| Q.1 | ARJUN |
| Q.2 | KAPOOR |
| Q.3 | KIRAN SAINI |
| Q.4 | ROHAN MEHTA |
| Q.5 | ANJALI GUPTA |
| Q.6 | VIVEK MALHOTRA |
| Q.7 | SIMRAN YADAV |
| Q.8 | MOHIT BANSAL |
| Q.9 | 348216, ₹97,500 |
| Q.10 | 895147, ₹82,600 |
Important Excel Note
For the traditional LOOKUP function, the lookup vector should be sorted in ascending order. In this assignment, the Employee IDs in Table 1 are intentionally not in ascending order, so for actual Excel practice, first sort the Employee ID column with the corresponding names, or use a sorted lookup range.
For exact matching without sorting, XLOOKUP is generally safer:
=XLOOKUP(784236,A2:A13,C2:C13)