ASSIGNMENT – 47

USE OF INDEX + MATCH – ADVANCED LOOKUP

Objective

In this assignment, you will learn how to use:

  • INDEX

  • MATCH

  • INDEX + MATCH

  • Exact Match

  • Lookup from Left

  • Dynamic Row/Column Lookup

  • Error Handling with IFERROR

EMPLOYEE DATA

Emp 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

LOOKUP IDs

Use the following Employee IDs for practical lookup:

Search ID
103
106
110
114
115
101
111
108

QUESTIONS

Q1. Find Employee Name

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

Functions:

INDEX + MATCH

Q2. Find Department

Find the Department of each employee using INDEX + MATCH.

Q3. Find City

Find the City of each employee using INDEX + MATCH.

Q4. Find Salary

Find the Salary of each employee using INDEX + MATCH.

Q5. Find Experience

Find the Experience of each employee using INDEX + MATCH.

Q6. Find Rating

Find the Rating of each employee using INDEX + MATCH.

Q7. Lookup Employee ID Using Employee Name

Find the Employee ID using the Employee Name.

Example:

Rahul → 103

Use INDEX + MATCH.

Q8. Lookup Salary Using Employee Name

Find the Salary using Employee Name.

Example:

Pooja → ₹46,000

Use INDEX + MATCH.

Q9. Find Employee Name from Salary

Find the Employee Name whose Salary is:

₹62,000

Use:

INDEX + MATCH

Q10. Find Employee Name from Rating

Find the Employee Name whose Rating is:

4.9

Note: More than one employee has a rating of 4.9. Observe which result INDEX + MATCH returns.

BONUS CHALLENGE ⭐

Q11.

Find the Employee ID of Simran.

Q12.

Find the Department of Rohan.

Q13.

Find the City of Priya.

Q14.

Find the Employee Name whose Experience is 7 years.

Q15. FINAL CHALLENGE

Create a lookup report:

Search ID Employee Name Department City Salary Experience Rating
103            
106            
110            
114            
115            
101            
111            
108            

Complete the entire report using INDEX + MATCH.

FORMULA PRACTICE

INDEX

INDEX returns a value from a specified position in a range.

Syntax

=INDEX(array,row_num)

MATCH

MATCH finds the position of a value in a range.

Syntax

=MATCH(lookup_value,lookup_array,0)

0 means Exact Match.

INDEX + MATCH

Example: Find Employee Name from Employee ID

=INDEX(B2:B16,MATCH(I2,A2:A16,0))

Where:

  • I2 = Search ID

  • A2:A16 = Employee ID column

  • B2:B16 = Employee Name column

Find Salary

=INDEX(E2:E16,MATCH(I2,A2:A16,0))

Find Employee ID from Employee Name

=INDEX(A2:A16,MATCH(I2,B2:B16,0))

This is important because INDEX + MATCH can look to the left, unlike a normal VLOOKUP.

INDEX + MATCH + IFERROR

To avoid #N/A when the value is not found:

=IFERROR(INDEX(B2:B16,MATCH(I2,A2:A16,0)),"Employee Not Found")

IMPORTANT CONCEPT

VLOOKUP

Normally searches from left to right.

INDEX + MATCH

Can search:

Left → Right

and also:

Right → Left

This makes INDEX + MATCH more flexible for advanced Excel lookup tasks.

STUDENT TASK

Complete all 15 questions using formulas.

Do not manually type the answers.

Use 0 in MATCH for exact matching:

=MATCH(lookup_value,lookup_array,0)

Verify every result carefully.

PreviousNext