INDEX & MATCH in Excel
Learn how INDEX and MATCH work together to retrieve information from Excel datasets. Practice exact lookups, row and column matching, two-way lookups, and dynamic searches using student records and course information.
1. What Is INDEX?
The INDEX function returns a value from a specified position within a range or array. You provide a row number and, optionally, a column number to identify the value to return.
=INDEX(array, row_num, [column_num])
Example: Retrieve Student Marks
Assume student marks are stored in B2:B6. The first value belongs to Samadhan, the second to Samiksha, and so on.
| Cell | Student | Marks | Position in B2:B6 |
|---|---|---|---|
| Row 2 | Samadhan | 85 | 1 |
| Row 3 | Samiksha | 92 | 2 |
| Row 4 | Pradnya | 76 | 3 |
| Row 5 | Omkar | 68 | 4 |
| Row 6 | Neha | 88 | 5 |
=INDEX(B2:B6,2)
INDEX returns the second value in the specified range. The position is relative to B2:B6, not the worksheet row number.
2. What Is MATCH?
The MATCH function searches for a value in a single row or column and returns its relative position within that range. It returns a position, not the corresponding value from another column.
=MATCH(lookup_value, lookup_array, [match_type])
The match_type argument controls the matching behavior:
| Value | Behavior |
|---|---|
0 |
Find an exact match. |
1 |
Find the largest value less than or equal to the lookup value. The lookup array must be sorted in ascending order. |
-1 |
Find the smallest value greater than or equal to the lookup value. The lookup array must be sorted in descending order. |
Example: Find Samiksha's Position
=MATCH("Samiksha",A2:A6,0)
If the names are in A2:A6 in the order shown above, the formula returns 2 because Samiksha is the second item in that range.
3. Combine INDEX and MATCH
INDEX and MATCH become especially useful when combined. MATCH identifies the relative position of a value, and INDEX returns the value at that position from another range.
Diagram: MATCH finds the position; INDEX retrieves the corresponding value.
Formula
=INDEX(B2:B6,MATCH("Samiksha",A2:A6,0))
Step-by-step calculation
MATCH("Samiksha",A2:A6,0)finds Samiksha's position and returns 2.- INDEX receives 2 as its row position.
INDEX(B2:B6,2)returns the second value in B2:B6.- The final result is 92.
4. Use a Cell Reference
A cell reference makes the formula reusable. Suppose E2 contains the student name you want to search for.
=INDEX(B2:B6,MATCH(E2,A2:A6,0))
If E2 contains Neha, the formula returns 88. Change E2 to Omkar and it returns 68, without changing the formula.
$A$2:$A$6 and
$B$2:$B$6 when copying formulas and keeping the
lookup ranges fixed.
=INDEX($B$2:$B$6,MATCH(E2,$A$2:$A$6,0))
5. Handle a Missing Match
If MATCH cannot find the lookup value, it returns #N/A. You can use IFERROR to display a more helpful message.
=IFERROR(INDEX(B2:B6,MATCH(E2,A2:A6,0)),"Student Not Found")
If E2 contains a name that is not present in A2:A6, the formula displays Student Not Found.
6. Two-Way Lookup Using INDEX and MATCH
A two-way lookup retrieves a value based on both a row label and a column heading. For example, you may want to find a student's score for a particular subject.
| Student | Excel | SQL | Python |
|---|---|---|---|
| Samadhan | 85 | 82 | 88 |
| Samiksha | 92 | 90 | 94 |
| Pradnya | 76 | 84 | 80 |
| Omkar | 68 | 72 | 75 |
| Neha | 88 | 86 | 91 |
Assume student names are in A2:A6, subject headers are in B1:D1, and marks are in B2:D6. Cell G2 contains a student name and H2 contains a subject name.
=INDEX(B2:D6,MATCH(G2,A2:A6,0),MATCH(H2,B1:D1,0))
Step-by-step explanation
- The first MATCH finds the row position of the student.
- The second MATCH finds the column position of the subject.
- INDEX uses both positions to return the value at their intersection.
7. INDEX and MATCH for Course Fees
The same technique can retrieve course fees from a list. Suppose course names are in A2:A5 and their fees are in B2:B5. Cell D2 contains the course to search for.
| Course | Fee (₹) |
|---|---|
| Advanced Excel | 8,000 |
| SQL Server | 12,000 |
| Core Java | 15,000 |
| Python | 14,000 |
=IFERROR(INDEX(B2:B5,MATCH(D2,A2:A5,0)),"Course Not Found")
If D2 contains SQL Server, the formula returns ₹12,000. These fee amounts are sample training data.
8. INDEX and MATCH vs XLOOKUP
| Feature | INDEX & MATCH | XLOOKUP |
|---|---|---|
| Exact match | Use MATCH with 0. | Exact match is the default. |
| Return from another range | INDEX returns the value at the matched position. | Uses a return array. |
| Missing value message | Can be handled with IFERROR or IFNA. | Has an optional if_not_found argument. |
| Two-way lookup | Combine row MATCH and column MATCH. | Can also use nested XLOOKUP formulas. |
| Compatibility | Available in many older Excel versions. | Requires a supported modern Excel version. |
Both methods are useful. INDEX and MATCH are especially valuable when learning position-based lookups and working with older Excel versions.
9. Common Mistakes and Solutions
| Problem | Cause | Solution |
|---|---|---|
| #N/A | MATCH cannot find the requested value. | Check the lookup value, spelling, and lookup range. |
| Wrong result | The INDEX return range does not correspond to the lookup range. | Verify that the ranges are aligned correctly. |
| Unexpected approximate match | MATCH uses 1 or -1 instead of 0. | Use 0 for an exact match. |
| Formula changes after copying | Relative references move when copied. | Use absolute references where appropriate. |
| Wrong two-way result | The row or column MATCH uses the wrong range. | Verify both lookup ranges and the INDEX array. |
10. Practical Exercises
Exercise 1: Student Marks
- Create a student table with names and marks.
- Use MATCH to find Samiksha's position.
- Use INDEX to retrieve the second mark.
- Combine INDEX and MATCH to retrieve Neha's marks.
- Use IFERROR to handle a missing student name.
Exercise 2: Two-Way Lookup
- Create a table of students and marks for Excel, SQL, and Python.
- Enter a student name in a separate cell.
- Enter a subject name in another cell.
- Retrieve the intersecting mark using INDEX and two MATCH functions.
Exercise 3: Course Fees
- Create a course fee list.
- Search for SQL Server using INDEX and MATCH.
- Test a course name that does not exist.
- Display Course Not Found when there is no exact match.
Important Points to Remember
- INDEX returns a value from a specified position.
- MATCH returns the relative position of a lookup value.
- Use MATCH type 0 for exact matching.
- Combine INDEX and MATCH to retrieve related information.
- Use two MATCH functions for a two-way lookup.
- Use IFERROR or IFNA to handle missing matches appropriately.
- Use absolute references when copying formulas across cells.
Summary
In this lesson, you learned how INDEX returns values, how MATCH finds relative positions, and how to combine both functions for exact lookups, two-way lookups, and course fee searches.
INDEX and MATCH provide a flexible way to retrieve data from structured worksheets. In the next topic, we will learn about SUMIFS and COUNTIFS for conditional calculations and counting.