The INDEX and MATCH functions in Excel are often used together to perform advanced lookups. They provide a flexible and powerful alternative to the more commonly used VLOOKUP or HLOOKUP functions, offering advantages like working with both vertical and horizontal data, as well as being more efficient in certain situations.
1. INDEX Function:
The INDEX function returns the value of a cell in a specified range based on its row and column numbers.
Syntax:
INDEX(array, row_num, [column_num])
- array: The range of cells or array from which you want to retrieve a value.
- row_num: The row number in the array from which to return the value.
- column_num (optional): The column number in the array (if the array is more than one column wide).
Example:
=INDEX(A1:C5, 2, 3)
This would return the value in the 2nd row and 3rd column of the range A1:C5.
2. MATCH Function:
The MATCH function searches for a specified value in a range and returns the relative position of that value within the range.
Syntax:
MATCH(lookup_value, lookup_array, [match_type])
- lookup_value: The value you want to search for.
- lookup_array: The range of cells in which to search for the value.
- match_type (optional): Determines how Excel matches the lookup_value with the values in the lookup_array.
- 1: Finds the largest value that is less than or equal to the lookup_value (requires the array to be sorted in ascending order).
- 0: Finds the exact match.
- -1: Finds the smallest value that is greater than or equal to the lookup_value (requires the array to be sorted in descending order).
Example:
=MATCH("Apple", A1:A5, 0)
This would return the position of “Apple” in the range A1:A5.
Using INDEX and MATCH Together:
You can combine INDEX and MATCH to look up values dynamically based on both row and column criteria.
Syntax:
=INDEX(return_range, MATCH(lookup_value, lookup_range, 0))
- return_range: The range of values to retrieve from.
- lookup_value: The value you want to find.
- lookup_range: The range to search for the lookup_value.
Example:
Suppose you have a table where column A contains product names, and column B contains their corresponding prices. You want to find the price of a specific product.
| A | B |
|---|---|
| Apple | 1.50 |
| Banana | 0.80 |
| Orange | 1.20 |
| Grape | 2.00 |
To find the price of “Orange”, you would use:
=INDEX(B1:B4, MATCH("Orange", A1:A4, 0))
- MATCH(“Orange”, A1:A4, 0) will return the position of “Orange” in the range A1:A4, which is 3.
- INDEX(B1:B4, 3) will return the value in the 3rd row of the range B1:B4, which is 1.20.
So, the result would be 1.20.
Advantages of Using INDEX and MATCH:
- Flexibility: Unlike VLOOKUP, you can search for data in any column or row, not just the leftmost column.
- Efficiency: INDEX/MATCH can be more efficient in larger datasets since it doesn’t need to scan entire rows or columns.
- No need for sorted data: With MATCH, you don’t need to have your lookup range sorted, unlike with VLOOKUP when using an approximate match.
These combined functions are incredibly versatile for a wide range of lookup tasks in Excel.