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.
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 365.
How to Use INDIRECT, INDEX, and MATCH Functions in Excel: 4 Suitable Examples
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.
- 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.
- INDIRECT(“Table1[“&A13&”]”) here in the INDIRECT function, “Table1[“&A13&”]” is the reference text.
- Lastly, using the INDEX function we are extracting data for the matched rows.
- 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.
- 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 sheets with multiple criteria 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 contain 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.
- 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.
- 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 returns 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. |
Download Practice Workbook
You may download the following Excel workbook for better understanding and practice it by yourself.
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.
<< Go Back to INDEX MATCH | Formula List | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
