# How to Use INDIRECT, INDEX, and MATCH Functions in Excel

In MS Excel, INDIRECT, INDEX, and MATCH functions are the most used functions. These functions can be used to find specific data from any given dataset for multiple purposes. Formulas using these functions can be used for various purposes in terms of searching and finding data in Excel. In this article, I will show we can use the INDIRECT, INDEX, and MATCH functions together for multiple purposes.

You may download the following Excel workbook for better understanding and practice it by yourself.

## Introduction to INDIRECT Function

The INDIRECT function is generally implemented to store a cell reference and then use the reference value with other functions to execute multiple operations in spreadsheets.

Objectives

The main goal of the function is to store data from the reference specified by a text string.

Syntax `=INDIRECT(ref_text, [a1])`

Arguments Explanation

Argument Required/Optional Explanation
ref_text Required Reference of a cell with A1 or R1C1 format.
[a1] Optional Format of the cell to select- A1 or R1C1

Version

• The INDIRECT function is available from Microsoft Excel 2007.
• Here, we will use Microsoft Excel 365.

## 4 Suitable Examples to Use INDIRECT Function with the Combination of INDEX and MATCH Functions in Excel

This article will demonstrate how to use the INDIRECT function with the INDEX and MATCH functions in different criteria to find data and extract it from different worksheets, search data based on condition, and apply the search to a table to fetch the data.

### Method 1: Merging INDEX and MATCH Functions with INDIRECT Function in a Table

Let’s have a sales information dataset for a company with the salesperson’s Name and Month. Now our task is to show all the sales of individuals in a single column. For this, we will use a formula using the INDEX, MATCH, and INDIRECT functions.

Steps:

• Firstly, select the C15 cell.
• Secondly, write down the following formula.
`=INDEX(INDIRECT("Table1["&B15&"]"),MATCH(\$C\$14,Table1[Full Name],0))`
• Thirdly, click Enter. Formula Breakdown
• Here our sales table is mainly a pivot table. Which is named Table1.
• MATCH(\$B\$12, Table1[Full Name],0) using this part we are checking if there are any matched names in the Name column. And 0 is used for exact matching. For more information about this function, you can visit this link.
• INDIRECT(“Table1[“&A13&”]”) here in the INDIRECT function, “Table1[“&A13&”]” is the reference text. To explore this function, you can check this link.
• Lastly, using the INDEX function we are extracting data for the matched rows. For more information, you can visit this link.
• Therefore, you will see the result for the first person in the C15 cell.
• After that, use the Fill Handle tool and drag it down from the C15 cell to the C20 cell. • So, you will get the following results.
• Then, copy the formula for the other columns too. • Finally, you will get all the results in the below image. ### Method 2: Combining INDIRECT, INDEX, and MATCH Functions to Find Data

Let’s consider the same dataset as above. Our task now is to calculate the total sales by entering the only Name in the input box.

Steps:

• Firstly, choose the D16 cell.
• Then, enter the following formula in cell D16
`=INDEX(E5:E12-INDIRECT("G5"),MATCH(D15,B5:B12,0))`
• After that, press Enter after choosing any name you want to enter in the D15 cell. Formula Breakdown
• The primary output value for the INDEX function is E5:E12-INDIRECT(“G5”), which is obtained and evaluated after matching.
• The INDIRECT function in Excel protects Cell References with the syntax INDIRECT(“G5”).
• MATCH(D15, B5:B12, 0) checks the dataset for a match with the specified name.
• Lastly, the INDEX function then extracts the resultant value.
• Lastly, you will get the output. ### Method 3: Nesting INDIRECT, INDEX, and MATCH Functions to Extract Data from Different Worksheets

Here, we will extract data from different worksheets by nesting the INDIRECT, INDEX, and MATCH functions.

Steps:

• So, let’s have three different worksheets with data named Data1, Data2, and Data3. All the worksheets are containing the Product Name, Quantities, Branch, and Price.
• Here is our first data set including  Product Name, Quantities, Branch, and Price. • Now, this is the second data set including  Product Name, Quantities, Branch, and Price (Product 21 – Product 26). • And, the below image shows our third data set including  Product Name, Quantities, Branch, and Price (Product 31 – Product 36). • Now our task is to search Product Prices by entering their Tab Name, Product Name, and Branch.
• Then, we apply the following formula in the C7 cell after writing all the above-mentioned names.
`=INDEX(INDIRECT("'"&C4 &"'!D5:D10"),MATCH( C5,INDIRECT("'"&C4&"'!B5:B10"),0),MATCH( C6,INDIRECT("'"&C4&"'!C5:C10"),0))`
• After that, press Enter. Formula Breakdown
• Firstly, MATCH(C6,INDIRECT(“‘”&C4&”‘!C5:C10”),0) is the column_num argument of the INDEX function which results in 1.
• Secondly, MATCH(C5,INDIRECT(“‘”&C4&”‘!B5:B10”),0) is the row-num argument of the INDEX function which results in 1.
• After that, INDIRECT(“‘”&C4 &”‘!D5:D10”) works as the array argument of the INDEX function which will result in the whole data set of the sheet or tab named Data1.
• Lastly, the INDEX function will figure out the data of the matched cell which is \$ 500.
• As a result, you will get the following result in the below image. ### Method 4: Merging INDIRECT, INDEX, and MATCH Functions to Search Data Based on Condition

Now let’s see how we can use criteria with the INDEX, INDIRECT, and MATCH functions. Again, we will use the same dataset as method 2, and along with the name, we will check the joining date as well.

Steps:

• Firstly, choose the D17  cell.
• Then, apply the following formula in cell D17 after entering any name, joining date.
`=INDEX(E5:E13-INDIRECT("G5"),MATCH(1,(B5:B12=D15)*(D5:D12=D16),0))`
• After that, hit Enter. Formula Breakdown
• The primary output value for the INDEX function is E5:E13-INDIRECT(“G5”), which is obtained and evaluated after matching. The Excel INDIRECT function locks Cell References using INDIRECT(“G5”).
• MATCH(1,(B5:B12=D15)*(D5:D12=D16),0) searches the dataset for a match with the specified name.
• Lastly, the INDEX function then extracts the resultant value.
• Therefore, the below image shows the result here. ## Things to Remember

Common Errors When they show
#VALUE error in INDEX All ranges must be on one sheet or the INDEX function return a #VALUE error.
Case-Sensitive The MATCH function is not case-sensitive.
#REF! In INDIRECT All the parameters used in the INDEX formula in excel, such as Row_num, Column_num, and Area_num, should refer to a cell within the array defined; otherwise, the INDEX function on Excel will return #REF! error value.

## Conclusion

These are some ways to use the INDIRECT, INDEX, and MATCH functions for various purposes in Excel. I have shown all the methods with their respective examples, but there can be many other iterations. I have also discussed the fundamentals of the functions used. If you have any other method of achieving this, then please feel free to share it with us. Hello! Welcome to my Profile. Currently, I am working and researching Microsoft Excel, and here I will be posting articles related to this. My last educational degree is BSc, and my program was Computer Science and Engineering from American International University-Bangladesh. I am a Computer Science graduate with a great interest in research and development. Always try to gather knowledge from various sources and try to make innovative solutions.

We will be happy to hear your thoughts  5 Excel Hacks You Never Knew  