Md. Abdullah Al Murad
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.
Download the Practice Workbook
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.
Further Readings:
- How to Use INDEX MATCH with Multiple Criteria in a Different Sheet (2 Ways)
- INDEX MATCH with 3 Criteria in Excel (4 Examples)
- INDEX MATCH across Multiple Sheets in Excel (With Alternative)
- Index Match Sum Multiple Rows
- Index Match with Multiple Matches
- INDEX MATCH Multiple Criteria in Excel (Without Array Formula)
