Chapter 17: MATCH and INDEX Functions

Introduction to MATCH and INDEX Functions
The MATCH and INDEX functions in Excel are vital for retrieving data efficiently from large datasets. When used individually, they can help locate values and fetch data. When combined, they offer a powerful solution for dynamic lookups and advanced data analysis. This chapter will explore both functions in detail, showing you how to use them effectively.

1. MATCH Function

Purpose:
The MATCH function is used to find the position of a specific value within a range. It helps you locate the row or column where the value appears.

Syntax:
MATCH(lookup_value, lookup_array, [match_type])

• lookup_value: The value you are searching for.
• lookup_array: The range of cells that you want to search.
• match_type: Specifies the match type. Use 0 for an exact match, 1 for less than, and -1 for greater than. Most commonly, you’ll use 0 to find an exact match.

Example:
Imagine you have a list of products in cells A1

and you want to find the position of “Banana.” If “Banana” is in cell A3, the formula =MATCH(“Banana”, A1:A5, 0) will return 3, indicating that “Banana” is the 3rd item in the range.

How to Use:

1. Select a cell where you want to display the result.
2. Enter the MATCH formula.
3. Replace lookup_value with the value you want to find.
4. Replace lookup_array with the range where you want to search.
5. Use 0 for an exact match.

2. INDEX Function

Purpose:
The INDEX function retrieves a value from a specified position within a range or array. It can return data from a single cell or an array of cells.

Syntax:
INDEX(array, row_num, [column_num])

• array: The range of cells or array from which you want to retrieve data.
• row_num: The row number within the array.
• column_num: The column number within the array (optional, used if you’re working with a multi-column range).

Example:
If you have a table with sales data in cells B1

, and you want to get the value from the 2nd row and 3rd column, use =INDEX(B1:D5, 2, 3). This will return the value in cell D2.

How to Use:

1. Select a cell where you want to display the result.
2. Enter the INDEX formula.
3. Replace array with the range of cells.
4. Replace row_num with the row number you’re interested in.
5. (Optional) Replace column_num with the column number if needed.

3. Combining MATCH and INDEX

Using MATCH and INDEX together allows you to perform complex lookups where the position of data varies.

Syntax:
INDEX(return_range, MATCH(lookup_value, lookup_range, 0))

• return_range: The range from which you want to return the value.
• lookup_value: The value you are searching for.
• lookup_range: The range where you are searching for the lookup_value.

Example:
Suppose you have a list of products in A1

and their corresponding sales figures in B1

. To find the sales figure for “Orange,” you would use:
=INDEX(B1:B5, MATCH(“Orange”, A1:A5, 0))
This formula finds “Orange” in A1

, then returns the corresponding sales figure from B1

.

How to Use:

1. Select a cell where you want the result.
2. Enter the combined MATCH and INDEX formula.
3. Replace return_range with the range containing the data you want to retrieve.
4. Replace lookup_value with the value to search for.
5. Replace lookup_range with the range where to search for the lookup_value.

4. MATCH with Multiple Criteria (Row and Column)

To perform a lookup based on both row and column criteria, combine INDEX with MATCH functions.

Syntax:
INDEX(return_range, MATCH(row_criteria, row_range, 0), MATCH(column_criteria, column_range, 0))

• return_range: The range of cells containing the values to return.
• row_criteria: The value to match in the row range.
• row_range: The range to search for the row criteria.
• column_criteria: The value to match in the column range.
• column_range: The range to search for the column criteria.

Example:
If you have a table with products as rows and regions as columns, and you want to find the sales figure for “Apple” in the “East” region, use:
=INDEX(B2:D5, MATCH(“Apple”, A2:A5, 0), MATCH(“East”, B1:D1, 0))
Here, B2:D5 is the range of sales figures, A2:A5 is the list of products, and B1:D1 is the list of regions.

How to Use:

1. Select a cell for the result.
2. Enter the combined INDEX and MATCH formula.
3. Replace return_range with the range containing your data.
4. Replace row_criteria and column_criteria with the values you are searching for.
5. Replace row_range and column_range with the ranges to search for the criteria.

Practice Assignment:

1. Basic INDEX and MATCH: Use INDEX and MATCH to find the price of a specific product from a list of products and their prices.
2. Combined Lookup: Combine INDEX and MATCH to retrieve the sales amount for a specific product based on its position in the list.
3. Row and Column Lookup: Use INDEX and MATCH to find the sales figure for a product in a specific region from a two-dimensional table.

Pro Tip: When using INDEX and MATCH together, ensure that your ranges are consistent and aligned correctly. Using named ranges for your data can simplify formulas and make them easier to understand and maintain.

This chapter covers how to use the MATCH and INDEX functions, both separately and together, to perform various data lookups. These skills will greatly enhance your ability to manage and analyze data efficiently. For further learning and practice, Anjni Computer Education provides additional resources to deepen your understanding of these essential Excel functions.