INDEX and MATCH in Excel

The INDEX and MATCH functions are powerful alternatives to VLOOKUP or HLOOKUP. They are often used together to perform more flexible and dynamic lookups.

---

1. INDEX Function

Purpose: Returns the value of a cell in a specific row and column within a given range.

Syntax:

=INDEX(array, row_num, [column_num])

• array: The range of cells to search.
• row_num: The row number of the value to return.
• column_num: (Optional) The column number of the value to return (used in 2D arrays).

---

Example:

Data:

ID	Name	Department	Salary
101	John Smith	IT	60000
102	Mary Johnson	HR	55000
103	Alex Brown	Finance	75000
104	Chris Lee	Marketing	52000

Task: Get the Salary of the employee in the 3rd row of the table.

1. Formula: =INDEX(B2:D5, 3, 3)
2. Explanation:
   • B2:D5: The range of the data.
   • 3: Row number (relative to the range).
   • 3: Column number (relative to the range).
3. Result: 75000

---

2. MATCH Function

Purpose: Returns the position of a value in a row or column.

Syntax:

=MATCH(lookup_value, lookup_array, [match_type])

• lookup_value: The value to find.
• lookup_array: The range to search in.
• match_type:
  • 0: Exact match.
  • 1: Closest smaller value (data must be sorted in ascending order).
  • -1: Closest larger value (data must be sorted in descending order).

---

Example:

Task: Find the row number of the employee with ID 103.

1. Formula: =MATCH(103, A2:A5, 0)
2. Explanation:
   • 103: The value to find.
   • A2:A5: The column to search in.
   • 0: Exact match.
3. Result: 3

---

3. Using INDEX and MATCH Together

Purpose: Combines the power of both functions to perform dynamic lookups.

---

Example:

Task: Find the Salary of the employee with ID 103.

1. Formula: =INDEX(D2:D5, MATCH(103, A2:A5, 0))
2. Explanation:
   • MATCH(103, A2:A5, 0): Finds the row number where ID 103 is located (3).
   • INDEX(D2:D5, 3): Retrieves the value in the 3rd row of the Salary column.
3. Result: 75000

---

Advantages of INDEX and MATCH Over VLOOKUP:

1. More Flexible: Can search in any direction (VLOOKUP only searches left-to-right).
2. Does Not Break on Column Changes: VLOOKUP depends on column numbers, which can change if columns are added or removed.
3. Dynamic: Allows lookup in multi-dimensional ranges.

---