Syntax. INDIRECT(reference,A1)
Definition. This function converts a text string into a reference.
Arguments
reference (required). Expects an expression that can be interpreted as a string. The function converts this string into a valid reference (a cell reference or named range).
A1 (optional). Must be evaluated as a logical value. Defines whether the reference argument is in A1 syntax (if the argument returns
TRUEor is omitted) or the R1C1 syntax (if the argument returnsFALSE).
Background. If the reference argument cannot be evaluated to a valid reference or name, the function returns the #REF! error.
If you want to use an external reference to another workbook, the workbook must be open.
Example. The following examples show how this function is used.
Using Cell Addresses. The second example of the ADDRESS() function demonstrates how to use INDIRECT() to convert a string created with ADDRESS(row_num,column_num) into a reference.
Note
The explanation of the INDEX() function shows that
INDEX(A:Z,26,1)
does the same as
INDIRECT(ADDRESS(26,1))
Investment Analysis. Assume that you have an investment analysis that displays an optimal strategy for different investments with certain risk levels. The higher the estimated risk of a single investment, the higher the return will be. Figure 10-5 shows a simplified approach.
You want the user to only use the words high, medium, and low to see the expected yield of an investment. You give the name high to cell C28 (the first of the cells with assigned yield values in percent), cell C29 is medium, and cell C30 is low. With the investment capital in C32, the formula
=C32*(1+INDIRECT(C33))
returns the expected yield in C34. This works because INDIRECT() converts the string entered by the user in C33 into a name that is a valid reference.