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

Get FREE Advanced Excel Exercises with Solutions!

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.

Note: While making this tutorial, we used Microsoft Excel 365.

## 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.
`=INDEX(C6:C17, MATCH(C19, B6:B17, 0))`

Therefore, we got the Product Name which is Circuit Board C45. ## 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.
`=INDEX(\$B\$6:\$F\$17, MATCH(B20&C20,\$C\$6:\$C\$17&\$E\$6:\$E\$17,0), MATCH(D19,\$B\$5:\$F\$5,0))`

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. Note: #N/A error means “no value available”. If you are using lookup functions to find a value and it is not in the list or range, your formula will likely return a #N/A error.

## 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.
`=INDEX(F6:F17,MATCH(B20&C20&D20,D6:D17&C6:C17&E6:E17,0))`

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.
`=INDEX(B6:B17,MATCH(B20,C6:C17,0))`

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.
`=INDEX(F6:F17, MATCH(TRUE, EXACT(C19, C6:C17), 0))`

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. Note: This is an array formula. So, if you aren’t a Microsoft 365 user, use CTRL+SHIFT+ENTER while applying the formula.

## 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.
`=INDEX(C6:C17, MATCH(MIN(ABS(F6:F17-C19)), ABS(F6:F17-C19), 0))`

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. Note: This is an array formula. So if you aren’t a Microsoft 365 user, use CTRL+SHIFT+ENTER while applying the formula.

## 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.
`=INDEX(\$F\$6:\$F\$16,MATCH(\$C\$18&"*",\$C\$6:\$C\$16,0))`

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. Note: If there are more lookup values in the dataset than one, the formula will display the partial match value that appears first in the dataset.

## 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.

• Select cell C20 and apply the formula below.
`=INDEX(C6:C17, MATCH(MAX(F6:F17), F6:F17, 0))`

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.
`=INDEX(C6:C17, MATCH(MIN(F6:F17), F6:F17, 0))`

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.

• Paste the following formula in cell C22 and press the Enter
`=INDEX(C6:C17, MATCH(AVERAGE(F6:F17), F6:F17,1))`

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

• You can enter 0 for the exact match. No sorting is required. If the value in your lookup array is exact to the average,
• Put 1 in the formula, if your lookup column is ordered ascending. The formula will find the greatest value that is less than or equal to the average value.
• Enter -1 if your lookup column is arranged in descending. The formula will find the least value greater than or equal to the average value.

## 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.
`=INDEX(B6:F17,MATCH(C19,C6:C17,0),0)`

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.

• Then enter the following formula in cell C22.
`=COUNTIF(INDEX(\$B\$6:\$F\$17,0,MATCH(B19,\$B\$5:\$F\$5,0)),C19)`

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.
`=SUM(INDEX(B6:E12, 0, MATCH(C14,B5:E5,0)))`

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.
`=SUMIFS(INDEX(\$D\$6:\$F\$17,,MATCH(\$D\$20,\$D\$5:\$F\$5,0)),B6:B17,\$B\$20,\$C\$6:\$C\$17,\$C\$20)`

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.

• 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.
`=INDEX(INDIRECT(B6&"[Sales]"),MATCH(\$C6,INDIRECT(B6&"[Date]"),0))`

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.
`=INDEX(\$C\$6:\$C\$13, SMALL(IF(\$C\$15=\$D\$6:\$D\$13, ROW(\$D\$6:\$D\$13)-ROW(\$D\$6)+1), COLUMN(A2)))`
• 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
`=IFERROR(INDEX(\$C\$6:\$C\$17,SMALL(IF(\$B\$20=\$D\$6:\$D\$17,ROW(\$D\$6:\$D\$17)-ROW(\$D\$6)+1),ROW(2:2))),"")`
• 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.
`=VLOOKUP(B20,B6:F17,5)`

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.
`=XLOOKUP(B20,B6:B17,C6:F17)`

Here the lookup value is AB3023002. Thus, the complete row of that specific cell will be returned by the formula. Note: Excel 2016 and Excel 2019 do not support XLOOKUP; however, you may use a workbook that was generated with the XLOOKUP function in it by someone else using a more recent version of Excel.

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.

## What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems. Aung Shine

My name is Aung. I have my B.Sc. degree in EEE. From now on, I will be working with Microsoft Excel and other useful software, and I’ll upload articles related to them. My current goal is to write technical contents for anybody and everybody that will make the learning process of new software and features a happy journey.

1. Reply Rich Saunders Apr 18, 2023 at 7:45 PM

How to Use INDEX MATCH Formula in Excel (9 Examples) – Problems encountered
1) Step #7 contains three (3) months your solution contains ONLY two (2) months- What about March
2) Step #8 DOES NOT WORK – when you type a formula into A (ONE) cell the results will NOT populate 2 cells to the right. What is the formula utilized to obtain the data within the 2nd & 3rd cell????????? Does this formula only work in a particular Excel Version? 365?? Also, the only way I could get the function to work for the “Salesman” – Frank response was to utilized Ctrl + Shift + Enter Correct???????????????/ If the user needs to utilize Ctrl + Shift + Enter – Would be great if you ADVISED us of this necessity

• Reply Shamima Sultana Apr 26, 2023 at 12:19 PM

Dear Rich Saunders,

Query 1: The method 7 works just fine. If you write March in F4, you will get the values for that month as well.

Query 2: The formula is universal for any Excel version. If you write the formula in any cell the formula will get you the entire row value for the name you will write in the F4 cell. For example, if you write “Nathan” in F4 cell and paste the formula in the A1 cell then the formula will write the values of the entire row( B8:D8) in the A1:A3 range.
Yes, we should have mentioned the Ctrl+Shift+Enter in the article. We will make sure of that in the next articles. As we used Microsoft 365 so it works by pressing Enter. We are sorry for that.

Regards
ExcelDemy Advanced Excel Exercises with Solutions PDF  