Excel Functions and Formulas, INDIRECT()

Aug 31, 2015 by azurous in Excel

The INDIRECT() function returns a reference specified by a text string. For example the formula below:

=INDIRECT("A1")

returns the value in cell “A1”. You can download the workbook related to this article here.

Jump To:

Syntax
Example 1, Get Value From List, Based on Row Index
Example 2, Get Value From Table Based on Row and Column Index
Example 3, R[1]C[1] Notation, Get Value From Table Based on Row and Column Index

–

Contents

Syntax:

=INDIRECT(Ref_text, [A1])

Ref_text: The address of the range or cell to return reference to.

A1: The parameter A1 is optional. If using the “A1:B1” notation pass True or just leave empty. If using the R[1]C[1] notation pass False.

–

Example 1, Get Value From List, Based on Row Index:

Lets say we have a list of data in column A. We want to be able to select a row and to display the value in that row in another cell. In the figure below the user has input the value “1” in cell C1. The text string “A1” is created in cell C2. Cell C3 uses the INDIRECT function and returns the value in cell A1 (row 1 of column A). For more information about adding (concatenating) text strings, please see the article here.

In the figure below the user has input the value “5” cell C1. Cell C2 creates the text string “A5”. Cell C3 returns the text in cell A5 using the INDIRECT() function:

In order to implement this, write the following formula in cell C2:

="A" & C1

and the following formula in cell C3:

=INDIRECT(C2)

–

Example 2, Get Value From Table Based on Row and Column Index:

Lets say we have a table of data in the range A1:D4. We want to be able to select a row and column index, and for the value that row and column to be displayed in another cell. In the example below the user selects the row index 1 and the column index 3. Row index 1 and column index 3 is cell C1. The value in cell C1 is displayed in cell G5:

In the example below the user selects the row index 3 and the column index 4. Row index 3 and column index 4 is the cell D3. The value in cell D3 is displayed in cell G5:

In order to implement this, you would need to write the following formula in cell G3:

=CHAR(G2+64)

The CHAR() function receives as input a numeric value. It then returns the character associated with that number. The number 65 is associated with the character “A”. The number 66 is associated with the character “B”. It continues up like this. So for the first example where the user selected column index “3” which is column “C”, CHAR(G2+64) would equate to CHAR(3+64) = CHAR(67). The number 67 is associated with the character “C”. For the second example when the user selected the column index 4, CHAR(4+64) or CHAR(68) returned “D”.

Add the following formula to cell G4:

=G3 & G1

The formula above adds (concatenates ) the text strings in cells G3 and G1 and creates a combined text string. So for the first example “C” & “1” = “C1”. For the second example “D” & “3” = “D3”. For more information about adding (concatenating) text strings please see Excel Functions and Formulas, Adding (Concatenating) Text Strings:

Add the following formula to cell G5:

=INDIRECT(G4)

The INDIRECT function returns a reference to the input string expression. In the first example the string expression was “C1” therefore the INDIRECT function returned the value in cell C1. For the second example the string expression was “D3” therefore the INDIRECT function returned the value in cell D3:

–

Example 3, R[1]C[1] Notation, Get Value From Table Based on Row and Column Index:

This example describes using the INDIRECT function with the R1C1 notation. In the cell G1 and G2 the user inputs the number of rows and columns to offset from cell G4. The value of that cell is returned in G4.

In the example below the user chooses to offset 3 columns to the left from G4. This would equate to the cell D4:

In the example below the user chooses to display the value in the cell 1 row up and 5 columns left to the cell G4. This would equate to the cell B3:

This could be achieved by writing the following formula in cell G3:

="R[" &G1&"]C["&G2&"]"

The formula above adds the text strings “R[“, Value in G1, “]C[“, value in G2 and “]”. For more information about adding (concatenating) text strings please see Excel Functions and Formulas, Adding (Concatenating) Text Strings:

And write following formula in cell G4:

=INDIRECT(G3, FALSE)

The “FALSE” parameter indicates that we are using the R1C1 notation:

You can download the workbook related to this article here.