How to use Excel's Indirect Function

Microsoft
1

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 in ref_text. If omitted (or set to TRUE), A1-style referencing is assumed. If set to FALSE, 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 the a1 argument is TRUE, the INDIRECT 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 in B11.

  • 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.