The INDIRECT
function in Excel provides a way to reference cells indirectly, using either the content of another cell or a text string. This function allows you to derive a cell reference from its textual representation. Consequently, you can alter a cell or sheet reference within a formula without needing to modify the formula itself.
The INDIRECT
function enhances the capabilities of other functions by providing the flexibility to establish dynamic cell references and perform calculations based on these changing references.
Syntax
The syntax for the INDIRECT
function is as follows:
=INDIRECT(ref_text, [a1])
Arguments:
The INDIRECT
function uses two arguments, with one being mandatory:
-
ref_text
- This required argument specifies the cell reference as text. It can also accept a named range. -
a1
- This optional argument specifies the reference style used inref_text
. If omitted (or set toTRUE
), A1-style referencing is assumed. If set toFALSE
, it uses R1C1-style referencing.
Important Characteristics of the INDIRECT
Function
Here are some important properties of the INDIRECT
function:
-
If the
ref_text
argument does not contain a valid cell reference,INDIRECT
will return a#REF!
error. -
INDIRECT
provides a method for referencing cells in closed workbooks, though doing so will result in a#REF!
error if the source workbook is closed. Ensure worksheet or workbook names that contain spaces are enclosed in single quotes. -
If the cell range defined in
ref_text
exceeds the row or column limits of the Excel worksheet, the function returns a#REF!
error. -
If you use the R1C1 reference style in
ref_text
and thea1
argument isTRUE
, theINDIRECT
function returns a#REF!
error.
Avoid creating circular references when using the INDIRECT
function, as this can cause circular dependency errors. Additionally, be aware that INDIRECT
is a volatile function, which means it recalculates whenever any change occurs in the worksheet. This can lead to performance issues when working with large datasets.
Examples of INDIRECT
Function
The INDIRECT
function lets users build dynamic formulas, hierarchical drop-down lists, and more. The examples below illustrate different uses of the function to improve data analysis and extraction.
Method 1: Using INDIRECT
with VLOOKUP
Using a combination of INDIRECT
and VLOOKUP
functions offers a way to perform lookups based on user-defined criteria. This is particularly helpful when working with multiple datasets that follow a structured naming convention.
Step 1: Define named ranges for each of your datasets, following a consistent naming convention. For instance, if you have sales data for January, February, and March, name the corresponding ranges as Jan_Sales
, Feb_Sales
, and Mar_Sales
, respectively.
Step 2: Create a summary table where users can input their criteria. Typically, this includes a cell for the lookup value (e.g., product name) and a cell for selecting the dataset (e.g., month).
Step 3: Construct the VLOOKUP
formula using INDIRECT
to dynamically define the lookup range based on the user’s selection. Concatenate the user-provided input (e.g., month) with the fixed suffix used in your named ranges (e.g., _Sales
). Here’s an example formula:
=VLOOKUP(B10,INDIRECT(B11&"_Sales"),2,FALSE)
In this formula:
-
B10
is the cell containing the lookup value (e.g., product name). -
B11
is the cell where the user selects the month. -
INDIRECT(B11&"_Sales")
constructs the range name dynamically based on the month selected inB11
. -
2
is the column number from which to return the value. -
FALSE
ensures an exact match.
Step 4: (Optional) Enhance usability by using Data Validation to create drop-down lists for the input cells (e.g., month, store location), ensuring users can easily select valid options.
Method 2: Creating Multi-Level Dependent Drop-Down Lists
The INDIRECT
function can be used to create multi-level dependent drop-down lists through data validation.
Step 1: Set up your data: Arrange your data so that the first column contains the names for your primary drop-down, and subsequent columns contain the corresponding options for each primary choice.
Step 2: Create named ranges: For each column (starting from the second), create a named range using the column’s header as the name.
Step 3: Create the first drop-down list: Select a cell where you want the first drop-down list. Go to the Data
tab and select Data Validation
. Choose List
under Allow
and enter the range name for your first column (e.g., =Team_Name
) in the Source
box.
Step 4: Create the dependent drop-down list: Select a cell for the second drop-down list. Go to Data Validation
again, choose List
, and enter the following formula in the Source
box, replacing $A$10
with the cell reference of your first drop-down list:
=INDIRECT($A$10)
Method 3: Creating Fixed Ranges
To ensure that Excel maintains a specific cell range irrespective of changes, you can use the INDIRECT
function to create dynamic cell references that remain fixed.
Step 1: Use the SUM
function along with INDIRECT
to specify the starting cell as a text string.
Step 2: Input the ref_text
argument in double quotes so it is interpreted as a text value. For example:
=SUM(INDIRECT("C2"):C10)
Here Excel considers “C2” as a string and therefore does not change even when there are updates in the dataset.
Method 4: Simple Use of INDIRECT
Function
To understand its functionality, take simple input values for both the arguments of the INDIRECT
function to gain a better understanding.
Step 1: Input the value of the ref_text
. The INDIRECT
function navigates to the provided cell which contains a cell reference. The function then returns the value in referenced cell.
Step 2: Another way to use the INDIRECT
function to retrieve the value is to use it as a cell reference in double quotes making it a text value.
Step 3: Highlight the use of R1C1 style cell referencing where the INDIRECT
function goes to a cell which contains the cell reference in R1C1 style. Set the value of the second argument to FALSE
indicating the cell referencing style.
Method 5: Creating Dynamic Named Cell References
When calculating totals for different categories, using dynamic named cell references can simplify the process and reduce manual adjustments.
Step 1: Instead of manually entering cell ranges in formulas, input cell ranges in separate cells. For example, if calculating the sum of marks for students, enter the cell range B2:B6
in cell B8
.
Step 2: Use the INDIRECT
function within a SUM
formula to reference the cell containing the range. For example:
=SUM(INDIRECT(B8))
Now you can change the cell range in cell B8
to calculate the total for each student.
Step 3: Create named ranges for each student’s data. Select the data range (e.g., B1:G6
) and use the Create from Selection
feature (Formulas tab > Create from Selection). Choose the top row as the naming criteria.
Step 4: Replace cell ranges with named ranges in the INDIRECT
function. Now the formula becomes:
=SUM(INDIRECT("Emily"))
Step 5: To make it more convenient, add a Data Validation
list in cell B8
to select the student’s name from a drop-down.
Method 6: Creating Dynamic Worksheet Reference with INDIRECT
Function
When data is stored in separate worksheets, you can use the INDIRECT
function to create dynamic references to these worksheets.
Step 1: Organize your data: Ensure that each worksheet is named meaningfully (e.g., after the student’s name).
Step 2: Construct the formula: Use the INDIRECT
function to build a text string that represents the complete reference, including the sheet name and cell range. The format is:
=INDIRECT("'"&sheetname&"'!"&"cell range")
Step 3: Use concatenation: Concatenate the sheet name with a single quote at the start and a single quote plus an exclamation mark at the end. Enclose the cell reference in double quotes. For example:
=SUM(INDIRECT("'"&$C$4&"'!"&"C2:C6"))
Method 7: Using INDIRECT
Function with R1C1 Referencing
Using the INDIRECT
function with R1C1 referencing, dynamically fetch data by specifying row and column numbers.
Step 1: Calculate the column number dynamically using the COUNTA
function to count the number of non-empty cells in a specific row. For example:
=COUNTA(7:7)
Step 2: Construct the INDIRECT
formula with R1C1 style referencing:
=INDIRECT("R7C"&COUNTA(7:7),FALSE)
Set the second argument to FALSE
to indicate the R1C1 reference style.
The INDIRECT
function is a powerful tool for creating flexible and dynamic spreadsheets. By mastering its syntax and applications, you can significantly enhance your ability to manage and analyze data in Excel.