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.

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

⏷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 matc*h,**-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**.

`=INDEX(C6:C17, MATCH(C19, B6:B17, 0))`

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** I****N****DEX **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**.

`=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 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**.

`=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 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.

`=INDEX(B6:B17,MATCH(B20,C6:C17,0))`

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

`=INDEX(F6:F17, MATCH(TRUE, EXACT(C19, C6:C17), 0))`

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.

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

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

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

Now follow the steps below:

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

`=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 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.

`=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 exactly the average.**Put 1 in the formula if your lookup column is ordered in ascending order. 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 order. 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 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**.

`=INDEX(B6:F17,MATCH(C19,C6:C17,0),0)`

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

`=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**, 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**.

`=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 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**.

`=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 **$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**.

`=SUMPRODUCT(INDEX(D6:F17,MATCH({"Chassis C200","Widget X100"},B6:B17,0),MATCH(C21,D5:F5,0)))+SUMPRODUCT(INDEX(D6:F17,MATCH({"Chassis C200","Widget X100"},B6:B17,0),MATCH(C22,D5:F5,0)))`

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:

`=INDEX(INDIRECT(B6&"[Sales]"),MATCH($C6,INDIRECT(B6&"[Date]"),0))`

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.

`=IF(COUNTIF($D$6:D7,D6)>1,"Duplicate of "&INDEX($C$6:C7,MATCH(D6,$D$6:D7,0)),"Original")`

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.

`=VLOOKUP(B20,B6:F17,5)`

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:

`=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.**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

- Use INDIRECT, INDEX, and MATCH Functions
- XLOOKUP vs INDEX-MATCH
- [Fixed!] INDEX MATCH Not Returning Correct Value
- INDEX MATCH Formulas with Returning Multiple Matches
- INDEX-MATCH Formula to Return Multiple Values Horizontally
- Use IF with INDEX & MATCH Functions
- Sum Multiple Rows Using INDEX MATCH Formula

**<< Go Back to Formula List | Learn Excel**

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

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