ASSIGNMENT 48 – ANSWER KEY
USE OF XLOOKUP – ADVANCED LOOKUP PRACTICE
Employee Data
| ID | Employee Name | Department | City | Salary | Experience | Rating |
|---|---|---|---|---|---|---|
| 101 | Aarav | Sales | Sirsa | 35000 | 3 | 4.2 |
| 102 | Meera | HR | Hisar | 42000 | 5 | 4.5 |
| 103 | Rahul | IT | Sirsa | 50000 | 7 | 4.8 |
| 104 | Pooja | Sales | Delhi | 46000 | 4 | 4.6 |
| 105 | Karan | HR | Hisar | 38000 | 2 | 3.8 |
| 106 | Simran | IT | Sirsa | 55000 | 6 | 4.7 |
| 107 | Mohit | Sales | Delhi | 62000 | 8 | 4.9 |
| 108 | Neha | HR | Hisar | 44000 | 4 | 4.3 |
| 109 | Arjun | IT | Sirsa | 48000 | 1 | 3.9 |
| 110 | Priya | Sales | Delhi | 52000 | 5 | 4.4 |
| 111 | Rohan | IT | Sirsa | 65000 | 9 | 4.9 |
| 112 | Ananya | HR | Hisar | 47000 | 6 | 4.6 |
| 113 | Vikas | IT | Delhi | 40000 | 3 | 4.0 |
| 114 | Riya | Sales | Sirsa | 51000 | 4 | 4.7 |
| 115 | Nitin | HR | Hisar | 35000 | 7 | 3.7 |
Q1. Find Employee Name using Employee ID
Formula:
=XLOOKUP(I2,$A$2:$A$16,$B$2:$B$16)| ID | Employee Name |
|---|---|
| 103 | Rahul |
| 106 | Simran |
| 110 | Priya |
| 114 | Riya |
| 115 | Nitin |
| 101 | Aarav |
| 111 | Rohan |
| 108 | Neha |
| 120 | #N/A |
Q2. Find Department
Formula:
=XLOOKUP(I2,$A$2:$A$16,$C$2:$C$16)| ID | Department |
|---|---|
| 103 | IT |
| 106 | IT |
| 110 | Sales |
| 114 | Sales |
| 115 | HR |
| 101 | Sales |
| 111 | IT |
| 108 | HR |
Q3. Find City
Formula:
=XLOOKUP(I2,$A$2:$A$16,$D$2:$D$16)| ID | City |
|---|---|
| 103 | Sirsa |
| 106 | Sirsa |
| 110 | Delhi |
| 114 | Sirsa |
| 115 | Hisar |
| 101 | Sirsa |
| 111 | Sirsa |
| 108 | Hisar |
Q4. Find Salary
Formula:
=XLOOKUP(I2,$A$2:$A$16,$E$2:$E$16)| ID | Salary |
|---|---|
| 103 | ₹50,000 |
| 106 | ₹55,000 |
| 110 | ₹52,000 |
| 114 | ₹51,000 |
| 115 | ₹35,000 |
| 101 | ₹35,000 |
| 111 | ₹65,000 |
| 108 | ₹44,000 |
Q5. Find Experience
Formula:
=XLOOKUP(I2,$A$2:$A$16,$F$2:$F$16)| ID | Experience |
|---|---|
| 103 | 7 Years |
| 106 | 6 Years |
| 110 | 5 Years |
| 114 | 4 Years |
| 115 | 7 Years |
| 101 | 3 Years |
| 111 | 9 Years |
| 108 | 4 Years |
Q6. Find Rating
Formula:
=XLOOKUP(I2,$A$2:$A$16,$G$2:$G$16)| ID | Rating |
|---|---|
| 103 | 4.8 |
| 106 | 4.7 |
| 110 | 4.4 |
| 114 | 4.7 |
| 115 | 3.7 |
| 101 | 4.2 |
| 111 | 4.9 |
| 108 | 4.3 |
Q7. Search Employee ID 120
To display a meaningful message instead of #N/A:
Formula:
=XLOOKUP(I2,$A$2:$A$16,$B$2:$B$16,"Employee Not Found")Answer:
Employee Not Found
This is one of the advantages of XLOOKUP: we can directly specify what should appear when the value is not found.
Q8. Find Employee ID using Employee Name
Formula:
=XLOOKUP(I2,$B$2:$B$16,$A$2:$A$16,"Employee Not Found")| Employee Name | Employee ID |
|---|---|
| Rahul | 103 |
| Pooja | 104 |
| Rohan | 111 |
Important:
XLOOKUP can look left or right.
This is easier than VLOOKUP because VLOOKUP normally cannot return a value from a column to the left of the lookup column.
Q9. Find Employee Name whose Salary is ₹62,000
Formula:
=XLOOKUP(62000,$E$2:$E$16,$B$2:$B$16,"Employee Not Found")Answer:
Mohit
Q10. Find Employee Name whose Rating is 4.9
Formula:
=XLOOKUP(4.9,$G$2:$G$16,$B$2:$B$16,"Employee Not Found")Answer:
Mohit
Important Observation:
There are two employees with rating 4.9:
-
Mohit → 4.9
-
Rohan → 4.9
XLOOKUP returns Mohit because he appears first in the data.
BONUS QUESTIONS
Q11. Find Salary of Simran
Formula:
=XLOOKUP("Simran",$B$2:$B$16,$E$2:$E$16)Answer:
₹55,000
Q12. Find Department of Rohan
Formula:
=XLOOKUP("Rohan",$B$2:$B$16,$C$2:$C$16)Answer:
IT
Q13. Find City of Priya
Formula:
=XLOOKUP("Priya",$B$2:$B$16,$D$2:$D$16)Answer:
Delhi
Q14. Find Employee Name with 7 Years of Experience
Formula:
=XLOOKUP(7,$F$2:$F$16,$B$2:$B$16,"Employee Not Found")Answer:
Rahul
Important Observation:
Two employees have 7 years of experience:
-
Rahul → 7 years
-
Nitin → 7 years
XLOOKUP returns Rahul because he appears first.
Q15. FINAL XLOOKUP REPORT
| ID | Employee Name | Department | City | Salary | Experience | Rating |
|---|---|---|---|---|---|---|
| 103 | Rahul | IT | Sirsa | ₹50,000 | 7 | 4.8 |
| 106 | Simran | IT | Sirsa | ₹55,000 | 6 | 4.7 |
| 110 | Priya | Sales | Delhi | ₹52,000 | 5 | 4.4 |
| 114 | Riya | Sales | Sirsa | ₹51,000 | 4 | 4.7 |
| 115 | Nitin | HR | Hisar | ₹35,000 | 7 | 3.7 |
| 101 | Aarav | Sales | Sirsa | ₹35,000 | 3 | 4.2 |
| 111 | Rohan | IT | Sirsa | ₹65,000 | 9 | 4.9 |
| 108 | Neha | HR | Hisar | ₹44,000 | 4 | 4.3 |
| 120 | Employee Not Found | Not Found | Not Found | Not Found | Not Found | Not Found |
Final Report Formula Example
Name:
=XLOOKUP(I2,$A$2:$A$16,$B$2:$B$16,"Employee Not Found")Department:
=XLOOKUP(I2,$A$2:$A$16,$C$2:$C$16,"Not Found")City:
=XLOOKUP(I2,$A$2:$A$16,$D$2:$D$16,"Not Found")Salary:
=XLOOKUP(I2,$A$2:$A$16,$E$2:$E$16,"Not Found")Experience:
=XLOOKUP(I2,$A$2:$A$16,$F$2:$F$16,"Not Found")Rating:
=XLOOKUP(I2,$A$2:$A$16,$G$2:$G$16,"Not Found")Key Learning
XLOOKUP Syntax:
=XLOOKUP(lookup_value,lookup_array,return_array,[if_not_found])XLOOKUP can:
-
Search vertically.
-
Search horizontally.
-
Look left or right.
-
Return a custom Not Found message.
-
Return the first matching value when duplicates exist.
-
Replace many common VLOOKUP/HLOOKUP use cases.