ASSIGNMENT – 47
ANSWER KEY – INDEX + MATCH
Data Range
-
Employee ID →
A2:A16 -
Employee Name →
B2:B16 -
Department →
C2:C16 -
City →
D2:D16 -
Salary →
E2:E16 -
Experience →
F2:F16 -
Rating →
G2:G16
Q1. Find Employee Name
Formula
=INDEX($B$2:$B$16,MATCH(I2,$A$2:$A$16,0))Answers
| Search ID | Employee Name |
|---|---|
| 103 | Rahul |
| 106 | Simran |
| 110 | Priya |
| 114 | Riya |
| 115 | Nitin |
| 101 | Aarav |
| 111 | Rohan |
| 108 | Neha |
Q2. Find Department
Formula
=INDEX($C$2:$C$16,MATCH(I2,$A$2:$A$16,0))Answers
| Search ID | Department |
|---|---|
| 103 | IT |
| 106 | IT |
| 110 | Sales |
| 114 | Sales |
| 115 | HR |
| 101 | Sales |
| 111 | IT |
| 108 | HR |
Q3. Find City
Formula
=INDEX($D$2:$D$16,MATCH(I2,$A$2:$A$16,0))Answers
| Search ID | City |
|---|---|
| 103 | Sirsa |
| 106 | Sirsa |
| 110 | Delhi |
| 114 | Sirsa |
| 115 | Hisar |
| 101 | Sirsa |
| 111 | Sirsa |
| 108 | Hisar |
Q4. Find Salary
Formula
=INDEX($E$2:$E$16,MATCH(I2,$A$2:$A$16,0))Answers
| Search 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
=INDEX($F$2:$F$16,MATCH(I2,$A$2:$A$16,0))Answers
| Search 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
=INDEX($G$2:$G$16,MATCH(I2,$A$2:$A$16,0))Answers
| Search 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. Lookup Employee ID Using Employee Name
Formula
=INDEX($A$2:$A$16,MATCH(I2,$B$2:$B$16,0))Example:
Rahul → 103
Other Answers
| Employee Name | Employee ID |
|---|---|
| Rahul | 103 |
| Simran | 106 |
| Priya | 110 |
| Riya | 114 |
| Nitin | 115 |
| Aarav | 101 |
| Rohan | 111 |
| Neha | 108 |
Important:
यहाँ INDEX + MATCH right-to-left lookup कर रहा है।
Q8. Lookup Salary Using Employee Name
Formula
=INDEX($E$2:$E$16,MATCH(I2,$B$2:$B$16,0))Examples
| Employee | Salary |
|---|---|
| Pooja | ₹46,000 |
| Rahul | ₹50,000 |
| Simran | ₹55,000 |
| Rohan | ₹65,000 |
Q9. Find Employee Name Whose Salary is ₹62,000
Formula
=INDEX($B$2:$B$16,MATCH(62000,$E$2:$E$16,0))Answer:
Mohit
Mohit’s Salary = ₹62,000
Q10. Find Employee Name Whose Rating is 4.9
Formula
=INDEX($B$2:$B$16,MATCH(4.9,$G$2:$G$16,0))Answer:
Mohit
Important Note:
There are two employees with a rating of 4.9:
-
Mohit → 4.9
-
Rohan → 4.9
But MATCH(...,0) returns the first exact match, so INDEX + MATCH returns:
Mohit
BONUS ANSWERS ⭐
Q11. Employee ID of Simran
Formula
=INDEX(A2:A16,MATCH("Simran",B2:B16,0))Answer:
106
Q12. Department of Rohan
Formula
=INDEX(C2:C16,MATCH("Rohan",B2:B16,0))Answer:
IT
Q13. City of Priya
Formula
=INDEX(D2:D16,MATCH("Priya",B2:B16,0))Answer:
Delhi
Q14. Employee Name with 7 Years of Experience
Formula
=INDEX(B2:B16,MATCH(7,F2:F16,0))Answer:
Rahul
Important Note:
There are two employees with 7 years of experience:
-
Rahul → 7 years
-
Nitin → 7 years
MATCH(...,0) returns the first exact match, therefore the answer is:
Rahul
Q15. FINAL LOOKUP REPORT
| Search 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 |
FINAL ANSWER SUMMARY
| Question | Answer |
|---|---|
| Q1 | Employee names found using INDEX + MATCH |
| Q2 | Departments found using INDEX + MATCH |
| Q3 | Cities found using INDEX + MATCH |
| Q4 | Salaries found using INDEX + MATCH |
| Q5 | Experience found using INDEX + MATCH |
| Q6 | Ratings found using INDEX + MATCH |
| Q7 | Employee ID can be found from Employee Name |
| Q8 | Salary can be found from Employee Name |
| Q9 | Mohit – ₹62,000 |
| Q10 | Mohit – Rating 4.9 |
| Q11 | Simran → 106 |
| Q12 | Rohan → IT |
| Q13 | Priya → Delhi |
| Q14 | Rahul – 7 Years |
| Q15 | Complete lookup report prepared |
QUICK REVISION
MATCH
Finds the position of a value:
=MATCH(103,A2:A16,0)If 103 is the 3rd item, MATCH returns:
3
INDEX
Returns the value from a particular position:
=INDEX(B2:B16,3)Returns the 3rd name from the range.
INDEX + MATCH
Together:
=INDEX(B2:B16,MATCH(103,A2:A16,0))Meaning:
Find 103 → get its position → return the corresponding name.
Most Important Point
MATCH(...,0) means:
Exact Match
And unlike standard VLOOKUP, INDEX + MATCH can perform left lookup as well as right lookup.