Excel INDEX and MATCH

INDEX and MATCH are two separate functions that are almost always used together as a powerful combination. They perform lookups — similar to VLOOKUP — but without VLOOKUP's key limitations. The INDEX-MATCH combination can search in any direction, look left or right, and handle more complex scenarios that VLOOKUP cannot manage.

Understanding INDEX and MATCH Separately

The INDEX Function

INDEX returns the value of a cell at a specific position within a range. It answers the question: "What is the value at row X, column Y of this range?"

Syntax

  =INDEX(array, row_num, [col_num])

• array: The range of cells to look in.
• row_num: The row number within the range.
• col_num (optional): The column number within the range.

Example

  | A      | B     | C     |
1 | Alice  | Sales | 5000  |
2 | Bob    | IT    | 6000  |
3 | Carol  | HR    | 4500  |

  =INDEX(A1:C3, 2, 3)
  → Row 2, Column 3 of the range
  Result: 6000  (Bob's salary)

  =INDEX(A1:A3, 3)
  → Row 3 of column A
  Result: "Carol"

The MATCH Function

MATCH returns the position (row or column number) of a value within a range. It answers the question: "At what position in this list does this value appear?"

Syntax

  =MATCH(lookup_value, lookup_array, [match_type])

• lookup_value: The value to find.
• lookup_array: The range to search in (must be a single row or column).
• match_type: 0 = exact match (most commonly used), 1 = less than, -1 = greater than.

Example

  List of names in A1:A3: Alice, Bob, Carol

  =MATCH("Bob", A1:A3, 0)
  → Bob is at position 2 in the list
  Result: 2

  =MATCH("Carol", A1:A3, 0)
  Result: 3

Combining INDEX and MATCH

MATCH finds the position of a search value. INDEX uses that position to return the corresponding value from another column. Together they perform a fully flexible lookup.

Syntax of the Combined Formula

  =INDEX(return_range, MATCH(lookup_value, lookup_range, 0))

Step-by-Step Explanation

1. MATCH finds what row the lookup value is in.
2. INDEX uses that row number to retrieve the value from the return range.

Example: Look Up Salary by Name

  | A      | B          | C      |
1 | ID     | Name       | Salary |
2 | 101    | Alice      | 50000  |
3 | 102    | Bob        | 60000  |
4 | 103    | Carol      | 45000  |

  Find the salary for "Bob":
  =INDEX(C2:C4, MATCH("Bob", B2:B4, 0))

  Step 1: MATCH("Bob", B2:B4, 0) → returns 2 (Bob is in row 2 of the range)
  Step 2: INDEX(C2:C4, 2) → returns the value at row 2 of C2:C4 = 60000

  Result: 60000

Why Use INDEX-MATCH Instead of VLOOKUP?

Advantage 1: Lookup in Any Direction

VLOOKUP can only return values to the right of the search column. INDEX-MATCH can return values from any column — including to the left.

Example: Look Left

  | A      | B    | C   |
1 | Name   | ID   | Dept|
2 | Alice  | 101  | Sales|
3 | Bob    | 102  | IT  |

  Find the Name (column A) when given ID 102 (column B):
  VLOOKUP cannot do this because column A is to the LEFT of column B.

  INDEX-MATCH can:
  =INDEX(A2:A3, MATCH(102, B2:B3, 0))
  Result: "Bob"

Advantage 2: Column Position Does Not Break the Formula

With VLOOKUP, if a column is inserted into the table, the col_index_num becomes wrong and must be updated manually. With INDEX-MATCH, each range is referenced directly, so inserting columns does not break the formula.

Advantage 3: Faster with Large Data

INDEX-MATCH can be faster than VLOOKUP when working with very large datasets, because VLOOKUP scans entire rows while MATCH only needs to search one column.

Two-Way Lookup with INDEX-MATCH-MATCH

For looking up both a row and a column simultaneously, MATCH can be used for both arguments of INDEX.

Syntax

  =INDEX(table_range, MATCH(row_value, row_headers, 0), MATCH(col_value, col_headers, 0))

Example

  | A     | B    | C    | D    |
1 |       | Jan  | Feb  | Mar  |
2 | North | 4000 | 5000 | 4500 |
3 | South | 6000 | 5500 | 7000 |
4 | East  | 3000 | 4000 | 3500 |

  Find sales for "South" in "Feb":
  =INDEX(B2:D4, MATCH("South", A2:A4, 0), MATCH("Feb", B1:D1, 0))

  → MATCH("South", A2:A4, 0) → 2 (South is in row 2)
  → MATCH("Feb", B1:D1, 0)   → 2 (Feb is in column 2)
  → INDEX(B2:D4, 2, 2)       → 5500

  Result: 5500

INDEX-MATCH with IFERROR

If the lookup value is not found, MATCH returns an #N/A error. Wrapping with IFERROR handles this gracefully.

  =IFERROR(INDEX(C2:C4, MATCH(E2, B2:B4, 0)), "Not Found")

  → If the name in E2 is not found, returns "Not Found" instead of an error.

Practical Example: Employee Lookup System

  Employee table: Columns = ID, Name, Department, Salary
  Lookup cell: G2 (employee ID entered by user)

  Return Name:
  =IFERROR(INDEX(B2:B100, MATCH(G2, A2:A100, 0)), "Not Found")

  Return Department:
  =IFERROR(INDEX(C2:C100, MATCH(G2, A2:A100, 0)), "Not Found")

  Return Salary:
  =IFERROR(INDEX(D2:D100, MATCH(G2, A2:A100, 0)), "Not Found")

  Changing the value in G2 updates all three results instantly.

Summary

• INDEX(array, row, col): Returns the value at a specific row and column position in a range.
• MATCH(value, range, 0): Returns the position (number) of a value within a range.
• Combined as =INDEX(return_range, MATCH(lookup_value, lookup_range, 0)), they perform a powerful and flexible lookup.
• Unlike VLOOKUP, INDEX-MATCH can look left, is not affected by column insertions, and supports two-way lookups.
• Use IFERROR to handle cases where the lookup value is not found.