Excel Functions and Formulas, INDIRECT()

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:

=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 Excel Functions and Formulas, Adding (Concatenating) Text Strings:

Excel, Formulas and Functions, INDIRECT, Example 1
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:

Excel, Formulas and Functions, INDIRECT, Example 1, Row 5
In order to implement this, write the following formula in cell C2:

="A" & C1

Excel, Formulas and Functions, INDIRECT, Example 1 Cell C2
and the following formula in cell C3:

=INDIRECT(C2)

Excel, Formulas and Functions, INDIRECT, Example 1 Cell C3


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:

Excel, Formulas and Functions, INDIRECT, Example 2

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:

Excel, Formulas and Functions, INDIRECT, Example 2, D3
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”.

Excel, Formulas and Functions, INDIRECT, Example 2 G3

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:

Excel, Formulas and Functions, INDIRECT, Example 2 G4
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:

Excel, Formulas and Functions, INDIRECT, Example 2 G5


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:

Excel, Formulas and Functions, INDIRECT, Example 3

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:

Excel, Formulas and Functions, INDIRECT, Example 3 2

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:

Excel, Formulas and Functions, INDIRECT, Example 3 G3

And write following formula in cell G4:

=INDIRECT(G3, FALSE)

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

Excel, Formulas and Functions, INDIRECT, Example 3 G4
You can download the workbook related to this article here.

See also:

If you need assistance with your code, or you are looking for a VBA programmer to hire feel free to contact me. Also please visit my website  www.software-solutions-online.com

Leave a Reply

Your email address will not be published. Required fields are marked *