# Formula Using INDIRECT INDEX MATCH Functions in Excel In MS Excel, INDIRECT, INDEX, MATCH functions are the most used functions. These functions can be used to find some 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 INDIRECT, INDEX, and MATCH functions combinedly for multiple purposes.

## What is 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 the spreadsheets.

The syntax of the function is like this:
`=INDIRECT(ref_text, [a1])`

Arguments

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

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

## 4 Ways to Use INDIRECT INDEX MATCH in Excel

### How to Use INDEX and MATCH In a Table With INDIRECT In Excel

Let’s have a sales information dataset of a company with Salespersons 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 INDEX, MATCH, and INDIRECT functions. Step 1: Enter the following formula in cell C15 and copy it down up to C20

`=INDEX(INDIRECT("Table1["&A13&"]"),MATCH(\$B\$12,Table1[Full Name],0))`

Formula Explanation

• 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 is any matched named 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

Step 2: Now copy the formula for the other columns too ### 2. Find Data by Combining INDIRECT and INDEX-MATCH

Let’s consider the same dataset as above. Our task now is to calculate total sales by entering the only Name in the input box. Step 1: Enter the following formula in cell D15 and press Enter

`=INDEX(E5:E12-INDIRECT("G6"),MATCH(D15,B5:B12,0))`

Formula Explanation

• In the INDEX function, E5:E12-INDIRECT(“G6”) is the main output value that will be extracted and calculated after matching. INDIRECT(“G6”) is locking Cell References with INDIRECT Function in Excel.
• MATCH(D15, B5:B12,0) is trying to get a match with the entered name with the dataset.
• Lastly, the INDEX function extracts the final value. Step 2: Enter any name and press Enter ### 3. Extract Data from Different Worksheets Using INDIRECT, INDEX, and MATCH Functions

Let’s have three different worksheets with data named Data1, Data2, and Data3. All the worksheets are containing Product Name, Quantity, Branch, and Price.   Now our task is to search Product Prices by entering their Tab Name, Product Name, and Branch. Step 1: Enter the following formula in cell C7 and press Enter Step 2: Now Enter tab name, product name, branch, and press Enter ### 4. Search Data Based on Condition Using INDIRECT, INDEX, and MATCH Functions

Now let’s see how we can use criteria with INDEX, INDIRECT, and MATCH functions. Again, we will use the same dataset as method 2. Here along with the name, we will check the joining date also. Step 1: Enter the following formula in cell D17 and press Enter

`=INDEX(E4:E12-INDIRECT("G5"),MATCH(1,(B4:B11=D14)*(D4:D11=D15),0))` Step 2: Enter any name, joining date, and press Enter ## Things to Remember

Common Errors When they show
#VALUE error in INDEX All ranges must on one sheet or INDEX will return a #VALUE error.
Case-Sensitive MATCH 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 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 used functions. If you have any other method of achieving this, then please feel free to share it with us.  