ASSIGNMENT – 20

Use of Formulas – INDEX + MATCH

Employee Salary Database

Enter the following employee data in Excel:

EMP NAME SALARY DEPARTMENT EMP ID
Amit ₹68,500 Sales EMP001
Neha ₹74,250 Operations EMP002
Rahul ₹52,800 Marketing EMP003
Priya ₹81,600 HR EMP004
Karan ₹59,450 Finance EMP005
Riya ₹63,750 IT EMP006
Mohit ₹87,300 Marketing EMP007
Anjali ₹72,400 IT EMP008
Vikas ₹55,900 Sales EMP009
Pooja ₹61,250 Finance EMP010
Arjun ₹91,500 Operations EMP011
Simran ₹78,350 Finance EMP012
Rohit ₹66,700 Marketing EMP013
Kavya ₹83,900 Sales EMP014
Nitin ₹57,600 IT EMP015
Divya ₹89,250 Operations EMP016
Manish ₹64,800 Sales EMP017
Tanya ₹76,500 Marketing EMP018
Suresh ₹70,350 HR EMP019
Meena ₹93,200 Finance EMP020

Search Table

Use INDEX + MATCH to find the salary for each Employee ID.

EMP ID SALARY
EMP006  
EMP014  
EMP003  
EMP019  
EMP011  
EMP008  
EMP020  
EMP005  
EMP017  
EMP012  

Questions

Q.1 Using INDEX + MATCH, find the Salary of EMP006.

Q.2 Find the Salary of EMP014 using INDEX + MATCH.

Q.3 Find the Salary of EMP003.

Q.4 Find the Salary of EMP019.

Q.5 Find the Salary of EMP011.

Q.6 Find the Salary of EMP008.

Q.7 Find the Salary of EMP020.

Q.8 Find the Salary of EMP005.

Q.9 Find the Salary of EMP017.

Q.10 Find the Salary of EMP012 using INDEX + MATCH.

Formula Pattern

To find the salary of EMP006, use:

=INDEX(B2:B21,MATCH(F2,D2:D21,0))

Formula Explanation

  • B2 → Salary range

  • D2 → Employee ID range

  • F2 → Employee ID to search

  • 0 → Exact Match

General Formula

=INDEX(Salary_Range,MATCH(Employee_ID,Employee_ID_Range,0))

Learning Objective

By completing this assignment, students will learn how to:

  • Use the INDEX function to return a value.

  • Use MATCH to find the position of an Employee ID.

  • Combine INDEX + MATCH for flexible data lookup.

  • Perform an exact-match lookup using 0.

PreviousNext