INDIRECT Function

What is the Excel INDIRECT Function?

The INDIRECT function is a feature in Excel that returns a cell reference to a range. This function helps lock in specific cells within a formula, so you can change a cell reference without changing the formula. It is labeled as a lookup and reference function.

This tool is useful for people in the financial analyst position, like me, when they need to build text values to link separate text strings into a valuable cell reference or build dynamic references for different calculations.

It also helps us put the address of one cell into another as a text string and get the value of that cell while acknowledging the second cell.

One of the biggest advantages of this tool is that even when you add new rows or columns, our indirect references won’t change. References also remain constant if you delete existing ones.

The syntax for the Indirect function in Excel is as follows:

=INDIRECT(ref_text, [a1])

Where

• ref_text = reference that is supplied as a text
• a1 = an optional boolean feature that indicates A1 or R1C1 style reference.
  • Default is TRUE = A1 style
  • Default is FALSE = R1C1 style

The indirect function is a volatile function, meaning that it could cause performance issues if the worksheets are larger or more complex.

Example of Excel INDIRECT Function

With this function, you’ll have to write down the reference in text form to get the indirect value of a cell. For example, suppose you have a value of 36 in cell D4. What you want to do here is input “D”&4 inside the parentheses.

Once you’ve imputed the reference inside the syntax, hit the enter key, and you get 36 as your answer.

Next, we’ll review this function in greater detail by providing more examples. Below is a screenshot of examples used to calculate using INDIRECT.

As you can see from the first example, by putting the formula INDIRECT (“E”&3), we get 12 as the answer, which means that it refers to the value in cell E3.

Next, suppose you want to find the indirect value of a row. Using row G as an example, you use the ROW function to find the indirect number. It returns 88 as the answer because you put the answer in row 4, and it returned the value from column G in the 4th row.

Then, you want to find the sum of a specific row. Using the 4th row as an example, with cells (“E4:G4”) being referenced, we got an answer of 155 because it adds the three numbers together as an indirect number (28 + 39 + 88 = 155).

Finally, you want to know the average within a specific row using the indirect function. For example, using the 5th row as our reference (“E5:G5”), we got an answer of 59, as it adds the three numbers in cells E5 through G5 (75, 57, 45) and divides them by 3.

INDIRECT Function vs. INDEX function

Now that we have a pretty good understanding of how the Indirect function works, you’re probably wondering if there is another function that does something similar but faster and easier. There is; it’s called Index.

The index function in Excel operates as a tool that returns the value from a specified reference within an array or range. For example, financial analysts use this tool to look up and return the sum of a column.

The syntax for the Index function in excel is

=INDEX(array, row_num, [col_num])

Where

• array = specified range of cells you chose to reference.
• row_num = the row number from that specified array. 
• col_num = the column number within that specified range.

We will go over how to calculate Index by providing a step-by-step guide and provide some examples to go along with it. For example, suppose you have a row with 3 numbers (4,12, 25), and you want to find the value in the 2nd column of that row.

First, we would input cells D2:F2 for our array, then put 1 for the row number as it's the first (and only) row that’s being referenced. Next, we put 2 for the col_num as we want the 2nd number in the referenced array.

Once we’ve inputted the arguments in the parentheses, we return with 12 as our answer.

We will go over how to use the Index function in greater detail by providing more examples. Below is a screenshot using examples of both INDIRECT and INDEX.

Suppose you wanted to determine the value of the first number of the first row. Using Index, we selected E3:G5 for our array and input 1 for the row_num and 1 for the col_num, and we got 12 as our answer.

Next, you want to determine the value for the 3rd column in the 2nd row. Again, using the same methods we did to calculate the first problem, we use E3:G5 for our array, 2 for the row_num, and 3 for col_num, and we return 88 for our answer.

Then, suppose you wanted to determine the value in the 2nd column in the 3rd row. Selecting E3:G5 for our array again, we input 3 in the row_num category and 2 in the col_num and return 57.

Finally, you want to find the index of the 2nd value from only one column. Using the Index formula, we input E3:E5 as an example and put 2 for the row_num as we get 28.

Researched and authored by Marcu Andrei Dumitrescu | LinkedIn

Reviewed and edited by Parul Gupta | LinkedIn

Free Resources

To continue learning and advancing your career, check out these additional helpful WSO resources:

• CHOOSE Function
• HLOOKUP Function
• INDEX Function
• MATCH Function
• OFFSET Function