How to Use INDEX MATCH in Excel (13 Suitable Ways)

Using the combination of the INDEX and MATCH functions in Excel means using these two functions together as a powerful combination for looking up and retrieving data from a table.

In this free Excel tutorial, you will learn how to apply the combination of INDEX and MATCH functions for 13 practical uses.

For example, the following dataset shows the details (Product Name, Category, Manufacturer, and Price) of each Product ID. We have determined the Price by applying two-way lookup and case-sensitive lookup. Lastly, we combined the INDEX and MATCH functions to do the left lookup. Left lookup is not possible with the VLOOKUP function.

In this blog post, you will learn how to apply INDEX MATCH in Excel for

-Two-way lookup
-Lookup value based on multiple criteria.
-Left lookup
-Case-sensitive lookup
-Finding closest match
-Finding partial match
-Finding maximum, minimum, and average values
-Extract values of an entire row
-Count values based on specific criteria.
-Sum multiple rows
-Lookup value across multiple sheets
-Returning multiple matches

Besides, we have shown the alternatives to INDEX and MATCH functions which are VLOOKUP and XLOOKUP functions. We can sum multiple rows based on single and multiple criteria and return multiple matches horizontally and vertically.

⏷The Basics of INDEX and MATCH Functions
  ⏵The INDEX Function
  ⏵The MATCH Function
⏷How to Use Combination of INDEX and MATCH Function
⏷ 13 Examples of Using INDEX MATCH 
⏷ Two-Way Lookup
⏷ Lookup Value Based on Multiple Criteria
⏷ Lookup Value from Left
⏷ Lookup Case-Sensitive Value
⏷ Finding Closest Match
⏷ Finding Partial Matched Data Using Wildcard Characters
⏷Find Minimum, Maximum, and Average Values
⏷Extract Values of Entire Row
⏷ Count Values for Specific Criteria
⏷ Sum Multiple Rows
  ⏵ Sum Multiple Rows for a Single Criteria
  ⏵ Sum Multiple Columns for Multiple Criteria
⏷Apply INDEX MATCH Formula Across Multiple Sheets
⏷How to Return Multiple Matches
  ⏵Return Match Values Horizontally
  ⏵Return Match Values Vertically
⏷Alternatives to INDEX MATCH
  ⏵Use VLOOKUP Function
  ⏵Use XLOOKUP Function

The Basics of INDEX and MATCH Functions in Excel

The INDEX Function:

The INDEX function returns a value or a reference to a value from a table or range of values.

Syntax:

=INDEX (array, row_num, [column_num])

• array: The range of cells from where we will retrieve the data; Required.
• row_num: The reference row number from which data should be returned;
• column_num: The reference column number from which data should be returned;

The MATCH Function

The MATCH function searches for a specified value in a range of cells and then returns the position of that value in that range.

Syntax:

MATCH(lookup_value,lookup_array,[match_type])

• lookup_value: The value that you want to match in the range of cells;
• lookup_array: The range of cells from where it’ll search for the lookup_value;
• [match_type]: Here, 1= the value less than the exact match, 0= the Exact match, -1= the value greater than the exact match

The dataset includes details about some mechanical and electrical products. Each row provides Product ID, Product Name, Category, Manufacturer, and Price. We will extract specific values from the dataset by applying the combination of INDEX and MATCH functions.

How to Use Combination of INDEX and MATCH Function in Excel

By using the combination of INDEX and MATCH functions, you can retrieve specific values. Using the Product ID, we will search for the Product Name of that ID. To do that follow the steps:

• Write this formula in C20.

Therefore, we got the Product Name which is Circuit Board C45.

13 Examples of Using INDEX MATCH in Excel

1. Two-Way Lookup Using INDEX MATCH Functions

Two-way lookup is mainly used to look up values in a table using both rows and columns. We will use the Product Name and Manufacturer column to find out the Price of that product.  Here we will show how to perform a two-way lookup. Follow the steps:

• Paste the following formula in cell D20.

Therefore, the formula returns the Price of the given Product Name and Manufacturer which is $50.00.

In the following GIF, you can see that the Price has changed after changing the value of the Product Name and the Manufacturer.

2. Lookup Value Based on Multiple Criteria Using INDEX MATCH Functions

When working with large datasets, you may sometimes find yourself in a situation where you need to find something, but you don’t have a unique identifier for the search. In this case, a lookup with several conditions is the only solution. Here we are going to show you how to look for values based on multiple criteria using INDEX and MATCH.

We took three criteria here: Category, Product Name, and Manufacturer. By defining these 3 criteria, we will search for the Price of the product.

• Apply the following formula in cell E20.

After applying the formula, you will find the price is $50.00.

If you change the criteria of the product, the price will change according to that. You can observe that in the GIF below.

3. Lookup Value from Left in Excel

When we want to extract values using the VLOOKUP function, we can find the value by searching to the right of the match value only. To look up a value in any column and return the corresponding value to the left, we can use the combination of the INDEX and MATCH functions instead of the VLOOKUP function.

Here we will look for Product ID by using the column of Product Name. As the Product ID is situated at the left of the Product Name, we need to do a left lookup. To do that follow the steps below:

• First, Select cell C20.
• Write the following formula and then press the Enter button.

Therefore, you will find the Product ID is AB3023001.

If you change the value of the Product Name, the Product ID will change as a result.

4. Lookup Case-Sensitive Value Using INDEX MATCH

If you have a dataset that contains both uppercase and lowercase letters, you can follow this method to do a case-sensitive lookup.

Here we have two different but similarly pronounced product names which are Chassis C200 and Chassis c200. They are different in terms of upper case (C) and lower case (c) letters. We want to know the price of these two products by defining the Product Name. We need to do a case-sensitive lookup here.

To perform case-sensitive lookup we will use the EXACT function along with the INDEX and MATCH functions. Follow the steps:

• Apply the formula in cell C20.

Therefore, you will get a price which is $200.00.

In the following GIF, you can see that we have changed the C200 to c200. The corresponding value has also changed.

5. Finding Closest Match Using INDEX MATCH

Sometimes, we might not receive a lookup value in the lookup array that exactly matches. Then, we should look for the most similar match. It happens whenever we look for numerical values.

Here we will try to find the Product Name of the closest match for the Price: $51.00. Now follow the following steps to find the closest match:

• Apply the formula in cell C20.

As a result, you will find the answer is Widget X100.

If you change the Price, the Product Name will change according to that. You can observe the GIF below.

6. Finding Partial Matched Data Using Wildcard Characters in INDEX MATCH Formula

Occasionally, we want a partial match when looking for a value since the value name is too large. In that situation, we can use an asterisk (*), which is a wildcard character, to find the partial match for a lookup value. We will write the partial Product Name (Chassis instead of Chassis C200) and the Price should be extracted.

Follow the steps below to do that:

• Select cell C19 and apply the following formula.

The outcome shows the price of Chassis C200 which is $200.00.

If you change the lookup value for the partial match. The outcome will change accordingly. You can see that in the following GIF.

7. Find Minimum, Maximum, and Average Values Using INDEX MATCH

In this example, we will determine the Product Name of the Minimum, Maximum, and Average Price values by using the INDEX, MATCH, MAX, MIN, and AVERAGE functions.

First, we will find the Product Name in the dataset with the highest value. We can find the maximum value of a column by using the MAX function combined with the INDEX and MATCH functions.

Now follow the steps below:

• Select cell C20 and apply the formula below.

Thus, Engine E500 is the product with the Maximum price.

Similarly, we can find the minimum price of the product using the MIN function.

• Paste the following formula in cell C21.

Thus, Bolt Set B10 is the product with the Minimum price.

We can also find the closest average value by combining the AVERAGE, INDEX, and MATCH functions. We need to change the dataset’s numerical values in either ascending or descending order in order to do that. We have changed our dataset into ascending order.

Now follow the steps below:

• Paste the following formula in cell C22 and press the Enter

So, the item with the closest average price is the Widget X100.

8. Extract Values of Entire Row with INDEX MATCH Formula

Sometimes we need to see an entire row of a particular cell. Observing a particular row inside a huge dataset is time-consuming. We can easily do that by using INDEX and MATCH functions.

Here, we want to extract the entire row by defining the Product Name. We will apply a simple formula to do that:

• Apply the following formula in cell C20.

As a result, you will get the full row where the Capacitors containing cell is located.

After changing the Product Name, the entire row is changed. You can see that in the GIF below.

9. Count Values for Specific Criteria Combining INDEX MATCH and COUNTIF Functions

In this method, we will show how to count values based on multiple criteria. Here We will count the number of products in the Electronics or Mechanical categories in the Category column of our dataset.

Follow the steps:

• Then enter the following formula in cell C22.

In the outcome, you can see that the total number of products in the Mechanical category is 5.

If you change the category from Mechanical to Electronics, the outcome will be changed accordingly. The result is now 7, you can see that in the GIF below.

10. Sum Multiple Rows Using INDEX MATCH

Often, we have to do conditional sums. To do this, we can combine the INDEX and MATCH functions for several columns with the SUMIFS function.
In this section, we will sum multiple rows for two cases: sum multiple rows for single criteria and sum multiple rows for multiple criteria.

10.1 Sum Multiple Rows for a Single Criteria

We have made some changes to our dataset while working on this method. Here, we will sum multiple rows based on Category. We want to calculate the total price based on the 3 columns defining different categories: Electronics, Mechanical, and Hardware. We will use the combination of SUM, INDEX, and MATCH functions to do this. Now follow the instructions below:

• Now, apply the following formula in cell C18.

So the total Price of the Electronics is $295.

You can easily get the total Price of the products of different categories by changing the category in cell C14. See the GIF below for a better understanding.

10.2 Sum Multiple Columns for Multiple Criteria

We covered the topic of summing several rows for single criteria in the previous section. But now we will show you how to sum multiple rows for multiple criteria by using SUMIFS, INDEX, and MATCH functions.

Here, we have the Category, Product Name, and price based on 3 dates: Jan-24, Feb-24, and Mar-24. So, we want to calculate the total Price by defining 3 criteria: Category, Product Name, and Month. Now follow the steps:

• Now apply the following formula in cell E20.

The output is $159.00.

After changing the multiple criteria, the product price will change accordingly.

11. Apply INDEX MATCH Formula Across Multiple Sheets

In this method, we will retrieve data using a formula with multiple criteria in different sheets. We can easily retrieve data from other sheets by using the INDEX, MATCH, and INDIRECT functions. To apply this formula, we have made a new dataset.

Here, we have 3 worksheets named: Electronics Sales Data, Mechanical Sales Data, and Hardware Sales Data. Each worksheet has the columns Product ID, Category, Data, and Sales of each product.

We want to get the Sales data of different worksheets by defining the Category and Date in this method.

To do that first, we must change the dataset into table format.

Now follow the instructions below:

• Select the dataset (B5:E12) >> Select the Home tab.
• From the Styles group of commands, select the Format as Table option.

• Create Table pop-up window will appear.
• Check the box of My table has headers as we included the header column while selecting the range.
• Then press OK.

• Then choose a table color that you like.
• Next, Click on the Table Design.
• Change the Table Name to Electronics.

Create tables for the other worksheets: Mechanical Sales Data and Hardware Sales Data.

• Create a new sheet and give it a name (we named it Summary). Then, format the sheet like the following.
• Now, select cell D6 in the Summary sheet and apply the following formula.

The Sales of the Electronics category of Date 01-Dec-23 is $50.00.

The sales value will adjust according to the sheet if you modify a category’s date. You can see that in the GIF below.

12. How to Return Multiple Matches Using INDEX MATCH in Excel

In this method, we will look for multiple values based on multiple criteria. We will return the values in two ways, horizontally and vertically.

12.1 Return Match Values Horizontally

Here we will search for all the products of a specific category. We will return all the products of that particular category horizontally. Now do the following actions.

• Select cell C18 and paste the following formula.

• To get the entire row, use the Fill Handle tool horizontally up to cell F18.

The formula will return all the products of the Mechanical category horizontally.

See the GIF below for a better understanding.

12.2 Return Match Values Vertically

In the previous method, we returned the value of a specific category horizontally. Here we are going to show you return the match value of a specific category vertically.

• Select cell C20 and apply the following formula

• Use the Fill Handle tool vertically to see all the products.

The formula will show all the products of the Electronics category vertically.

When you modify the category, the matching goods will change as well. See that in the GIF below.

13. Alternatives to INDEX MATCH in Excel

Instead of using the INDEX and MATCH functions, we can use VLOOKUP and XLOOKUP functions to retrieve data. These functions are also handy in some cases. We will discuss how to use these two functions in this method.

1. Use VLOOKUP Function

VLOOKUP means Vertical lookup. Excel uses this function to look for a specific value in a column. We can only search by looking at the right side of the defined range with the VLOOKUP function. We want to extract the price of a product using the column number. We will define the Product ID and the Price will be shown. Now follow the instructions below:

• First, Select cell B20.
• Then, paste the following formula and press the Enter button.

The formula will return the Price which is $50 of Product ID no AB3023001.

2. Use XLOOKUP Function

The XLOOKUP function is another handy alternative to the INDEX and MATCH functions. We will show an entire row using the XLOOKUP function here. Here, we will define the Product ID and the entire row of the defined Product ID will be shown. To do that, follow the steps below:

• Select cell C20 and apply the following formula.

Here the lookup value is AB3023002. Thus, the complete row of that specific cell will be returned by the formula.

Download Practice Workbook

You can download the workbook, where we have provided a practice section on the right side of each worksheet. Try it yourself.

In this article, you learned about different uses of combining the INDEX and MATCH functions like two-way lookup, left lookup, lookup value based on multiple criteria, case-sensitive lookup, finding the closest match, count and Sum values based on multiple criteria, lookup values across multiple sheets, and return match values horizontally and vertically.

You will also learn alternatives to using INDEX MATCH functions. You can also find partially matched data using the functions, you can also determine the average, minimum, and maximum values. I hope this article has helped you. Please leave a comment if you have any queries.