The Excel INDIRECT function is a Lookup & Reference formula that returns the reference specified by a text string. It is useful for when you want to convert text strings into valid references. In this guide, we’re going to show you how to use the Excel INDIRECT function and also go over some tips and error handling methods.
- All Excel versions
A string value that represents a valid reference. This reference can be in A1 or R1C1 style. Specify [a1] argument for R1C1 style.
Optional. Specifies the reference type.
Note that we’re using named ranges in our sample formulas to make them easier to read. This is not required.
Range and other functions
To create a valid external reference, make sure you follow these constraints:
- Cell/range reference should start with a “!” operator after workbook and/or worksheet name.
- Workbook name should be wrapped in brackets “[…]”.
- If there is a space or special character in the workbook or worksheet name, the workbook-worksheet pair should be in single quotes (‘…’). We recommend always using single quotes to avoid these types of issues.
A sample reference:
With dependent dropdowns, you can choose the ranges to be used in a list, and create a master list that contains these names. Using a name as ref_text argument of the INDIRECT function returns the range of the list.
For example, the cell F2 here has the value “Water”, which is also the name of the range B3:B7. As a result, =INDIRECT($F$2) formula returns the values in range B3:B7. If formula is applied as the Source of a Data Validation List, the values under the named range Water will be listed in the dropdown.
See How to create dependent dropdowns for more details.
- You can find more information about the INDIRECT function here: INDIRECT Formula and Proper Referencing
- Structural updates on rows or columns, like inserting or deleting, do not affect the INDIRECT function. =INDIRECT(A1:G12)
- If ref_text argument is not relevant with the [a1] argument, the INDIRECT returns a #REF! For example, =INDIRECT(“B3”,FALSE)
- If ref_text refers to a cell range outside the row limit of 1,048,576, or the column limit of 16,384 (XFD), the INDIRECT returns a #REF! error.
- If ref_text refers to another workbook the other workbook must also be open. Otherwise, the INDIRECT formula returns the #REF! error value.