How to Use Two INDEX and MATCH Statements to Return a Specific Value in Excel

In Excel, the INDEX function is used to return the value of a cell at a given position within a specified range. The MATCH function, on the other hand, returns the relative position of a value in a range. When combined, these two functions allow users to perform powerful lookups.

By using two INDEX and MATCH statements, you can retrieve a value from a table based on both row and column criteria. This technique is often referred to as a two-dimensional lookup because you’re looking up values in both rows and columns.

Function Breakdown:

• INDEX(array, row_num, [column_num]): Returns the value at the intersection of the specified row and column in an array.
• MATCH(lookup_value, lookup_array, [match_type]): Returns the relative position of a value in a one-dimensional array.

When combining these, you can create formulas like:

=INDEX(table_range, MATCH(row_value, row_range, 0), MATCH(column_value, column_range, 0))

Where:

• table_range is the entire data table.
• row_value is what you’re looking for in the rows.
• row_range is the range where you search for the row_value.
• column_value is what you’re looking for in the columns.
• column_range is the range where you search for the column_value.

Step-by-Step Procedures

1. Identify the Table: Determine the range of cells that contains the data you want to search in.
2. Set Your Lookup Values: Identify the row and column headers (or criteria) for which you want to find a specific intersection.
3. Write the MATCH Functions:
   • Use MATCH to find the relative position of your row criteria in the row header range.
   • Use MATCH again to find the position of your column criteria in the column header range.
4. Use INDEX to Return the Value: Combine the two MATCH functions inside INDEX to return the value at the intersection of the specified row and column.

Example

Scenario Setup

Suppose you have a table that lists monthly sales data for various products. You want to retrieve the sales figure for a specific product in a specific month.

You want to find the sales for “Product B” in the month of “February.”

Step-by-Step Calculation

Table Range: The entire table is in the range B2:D4.

Row Criteria: The lookup value is “February”, and the row range is A2:A4.

Column Criteria: The lookup value is “Product B”, and the column range is B1:D1.

Now, let’s break down the formula:

1. Find the Row Position (for “February”):

=MATCH("February", A2:A4, 0)

This will return 2, meaning “February” is the 2nd row in the range A2:A4.

2. Find the Column Position (for “Product B”):

=MATCH("Product B", B1:D1, 0)

This will return 2, meaning “Product B” is in the 2nd column of the range B1:D1.

3. Use INDEX to Retrieve the Sales Data:

=INDEX(B2:D4, MATCH("February", A2:A4, 0), MATCH("Product B", B1:D1, 0))

This formula looks up the value at the intersection of the 2nd row and 2nd column of the range B2:D4, which is 800. Hence, the sales for “Product B” in “February” is 800.

Result

The sales for Product B in February is 800.

Other Approaches

While INDEX and MATCH are extremely flexible, there are other methods to perform similar lookups in Excel:

1. VLOOKUP and HLOOKUP:

These functions allow you to perform lookups based on either a vertical (VLOOKUP) or horizontal (HLOOKUP) search. However, they are more limited than INDEX and MATCH since they require the lookup value to be in the first column or row of the table.

2. XLOOKUP:

The XLOOKUP function (available in newer versions of Excel) can handle both vertical and horizontal lookups. It’s more intuitive than using INDEX and MATCH combinations, allowing a single formula to return the result.

=XLOOKUP("February", A2:A4, XLOOKUP("Product B", B1:D1, B2:D4))

This method simplifies the lookup process and eliminates the need for nested MATCH functions.

Using two INDEX and MATCH statements provides a powerful way to retrieve specific values from a table in Excel by performing two-dimensional lookups. In the example scenario, we used these functions to find sales for a particular product in a given month. Other methods, such as XLOOKUP, offer more modern alternatives, but INDEX and MATCH remain highly flexible and are widely used for complex lookups.

Feel free to try this out on your own Excel data, and let me know if you need further clarification or additional examples!