How to Use INDEX and MATCH Functions in Excel (14 Suitable Examples)

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 a two-way lookup and a 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 and MATCH functions 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 of multiple rows
  -Lookup value across multiple sheets
  -Returning multiple matches

Besides, we have shown the alternatives to the INDEX and MATCH functions, which are the VLOOKUP and XLOOKUP functions. We can sum multiple rows based on single and multiple criteria and return multiple matches, both 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 for a Single Criteria
  ⏵ Sum Multiple Rows for Multiple Criteria
  ⏵Calculating Sum Based on Multiple Rows and Multiple Columns
  ⏵Apply INDEX MATCH Formula Across Multiple Sheets
  ⏵Finding Duplicate Values in Excel
⏷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 which 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 which 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 the Product ID, Product Name, Category, Manufacturer, and Price. We will extract specific values from the dataset by applying the combination of the INDEX and MATCH functions.

How to Use Combination of INDEX and MATCH Functions in Excel

By using the combination of INDEX and MATCH functions, you can extract 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

In this part, we will show you 14 useful examples of combining the INDEX and MATCH functions. Go through all of the examples provided below.

1. Two-Way Lookup Using INDEX MATCH Functions

A 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 columns 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 use INDEX MATCH with multiple criteria to look up values in Excel.

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

• Apply the following formula to 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 only find the matched value on the right side. To look up a value in any column from the left and return the corresponding value, we can use the INDEX and MATCH functions. Eventually, you will learn how to use INDEX and MATCH functions instead of the VLOOKUP.

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 that 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 use 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 uppercase (C) and lowercase (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 a 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 the 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 that the Product is Widget X100. Here, the Price of Widget X100 is $50.00 which is closest to $51.00.

If you change the Price, the Product Name will change accordingly. 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 result shows the Price of Chassis C200 which is $200.00.

Suppose 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 use the INDEX MATCH formula to find the minimum price of the product. In this case, we will combine the MIN function with the INDEX MATCH combination.

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

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 the 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 to cell C20.

As a result, you will get the full row for the Capacitors.

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 using the COUNTIF function 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. We will use the combination of INDEX, MATCH, and COUNTIF functions with multiple criteria.

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, as you can see in the GIF below.

10. Sum Multiple Rows for a Single Criteria

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.

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 three 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 to cell C18.

So the total Price of the Electronics is $295.

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

11. Sum Multiple Rows for Multiple Criteria

In the previous section, we covered the topic of summing several rows for a single criterion. But now we will show you how to sum multiple rows using INDEX MATCH Formula for multiple criteria. We will use the SUMIFS function in the INDEX MATCH combination.

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 three criteria: Category, Product Name, and Month. Now follow the steps:

• Now apply the following formula to cell E20.

The output is $913.00.

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

12. Calculating Sum Based on Multiple Rows and Multiple Columns

In this method, we will sum multiple rows based on four criteria (2 rows and 2 columns). We will look for total sales of Chassis C200 and Widget X100 products based on the dates Jan-24 and Mar-24. To do so, we need the SUMPRODUCT function. We will use the SUMPRODUCT with INDEX, and MATCH functions in Excel. Let’s follow the instructions below.

• First, select cell C23.
• Type the following formula in cell C23.

So, the total sales amount is $361.00.

13. Apply INDEX MATCH Formula Across Multiple Sheets

In this method, we will retrieve data using the INDEX MATCH formula across multiple 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 for each product.

We want to get the Sales data from 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.

• The Create Table pop-up window will appear.
• Check the box before 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 image:
• Now, select cell D6 in the Summary sheet and apply the following formula:

The Sales for the Electronics category on the 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.

14. Finding Duplicate Values in Excel with INDEX, MATCH, IF, and COUNTIF

In this method, we will find duplicate values by using the INDEX, MATCH, IF, and COUNTIF functions. There are some duplicate values in the Manufacturer column. The formula will indicate “Original” if there are no duplicates found and “Duplicate” if there are any. Now follow the instructions carefully.

• Select cell E6.
• Paste the following in cell E6 and press the ENTER button.

Then, using the Fill Handle tool, find the other duplicate values in the dataset.

Alternatives to INDEX MATCH in Excel

Instead of using the INDEX and MATCH functions, we can use the 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 for 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 have learned about the different uses of combining the INDEX and MATCH functions. You can now do two-way lookups, left lookups, lookup values based on multiple criteria, case-sensitive lookups, find the closest match, count and sum values based on multiple criteria, and lookup values across multiple sheets. You will also learn alternatives to using INDEX MATCH functions. You can find partially matched data using these functions and determine the average, minimum, and maximum values. I hope this article has helped you. Please leave a comment if you have any questions.

INDEX and MATCH Functions: Knowledge Hub

<< Go Back to Formula List | Learn Excel