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.
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.