INDEX-MATCH Function combination in Excel

INDEX-MATCH Function combination in Excel

Using the INDEX-MATCH function combination in Excel is a powerful way to look up values in a table. It’s an alternative to the commonly used VLOOKUP and offers more flexibility.

1. Understand Your Data:

  • Suppose you have a table with Employee IDs in column A, Names in column B, and Salaries in column C.
  • You want to find the Salary for a specific Employee ID.

data 5

2. Using the MATCH Function:

  • The MATCH function will find the position (row number) of the specific Employee ID in column A.
  • Syntax: =MATCH(lookup_value, lookup_array,[match_type])
  • If you want to find the salary for Employee ID 102:
    • Use MATCH to find the row number: =MATCH(102, A2:A3,0) This will return 2.


3. Using the INDEX Function 

  • The INDEX function will return the value from column C (Salary) that corresponds to the row number found by MATCH.
  • Syntax: =INDEX(array, row_num, [column_num])
  • Use INDEX to find the salary: =INDEX(C2:C3,2).  This will return 55000.


4. Using the INDEX Function with MATCH:

  • Combined with MATCH: =INDEX(C1:C10. MATCH(lookup_value, A1:A10,0))
  • Combine them: =INDEX(C2:C3. MATCH(102, A2:A3,0)) This will also return 55000.


  • The lookup_value is the value you want to find (e.g., Employee ID 102).
  • The lookup_array is the range where you want to find the lookup_value (e.g., A2:A3).
  • The array is the range from which you want to retrieve the value (e.g., C2:C3).
  • The [match_type] is optional. Use 0 for an exact match.
  • INDEX-MATCH works with both horizontal and vertical data and allows for more complex lookups, including looking to the left of the key column.

Leave a Comment