Formula Using INDIRECT INDEX MATCH Functions in Excel

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.

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.

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

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

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.

Find Data by Combining INDIRECT and INDEX-MATCH

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.

Formula using INDIRECT INDEX and MATCH Functions

Step 2: Enter any name and press Enter

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.

Extract Data from Different Worksheets Using INDIRECT, INDEX, and MATCH FunctionsExtract Data from Different Worksheets Using INDIRECT, INDEX, and MATCH FunctionsExtract Data from Different Worksheets Using INDIRECT, INDEX, and MATCH Functions

Now our task is to search Product Prices by entering their Tab Name, Product Name, and Branch.

Extract Data from Different Worksheets Using INDIRECT, INDEX, and MATCH Functions

Step 1: Enter the following formula in cell C7 and press Enter

Indirect Index Match

Step 2: Now Enter tab name, product name, branch, and press Enter

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.

Search Data Based on Condition Using INDIRECT, INDEX, and MATCH Functions

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

Indirect Index Match

Step 2: Enter any name, joining date, and press Enter

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:

Md. Abdullah Al Murad

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

Leave a reply

ExcelDemy
Logo