Excel INDIRECT Function
The Microsoft Excel INDIRECT function converts a text string to a valid reference.
The INDIRECT function will return a valid reference specified by a text string.
The INDIRECT function will return the #REF! error value when:
- The ref_text refers to another workbook that is not open;
- The ref_text refers to a cell range outside the row limit of 1,048,576 or the column limit of 16,384(XED).
The below examples is going to illustrate the common usage of INDIRECT function in Excel.
Example 1: INDIRECT cell reference
As below screenshot shown, the INDIRECT function =INDIRECT(B2) converted the text string in B2 into a valid cell reference, and then return the value of this cell reference (D2) as the final result.
- If the ref_text is enclosed in double quotation marks such as =INDIRECT("B2"), it will get the value in B2 directly.
- You can also combine a text string and a cell reference within the INDIRECT function as the below screenshot shown.
Here the INDIRECT function concatenates the ref_text (says D) and the value in B2 (says 2) as a valid cell reference D2, and returns the value in D2 as the result.
Example 2: INDIRECT with named range
The INDIRECT function also can directly refer to named ranges. As the below screenshot shown, there is a table containing several named ranges, and now you need to sum values in a certain named range (says Mon) with the combination of SUM and INDIRECT functions, please do as follows.
1. Enter the range’s name into a cell, here I enter Mon into cell C12. Select a blank cell, copy below formula into it and press the Enter key.
Now you have summed all values in the specified named range (Mon).