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

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

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

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

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

Now follow the steps below:

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

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,** 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 Nam***e*, 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.

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.

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

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