Excel INDIRECT Function – How To Use

The INDIRECT function allows the users to reference cells indirectly, depending on the contents of another cell or a text string. It lets you return the reference to a cell, based on its string representation, therefore we are able to change a cell reference (and sheet name) within a formula without changing the formula itself.

The INDIRECT function also improves the functionality of other functions as it provides the flexibility to create dynamic cell references and lets you perform calculations based on changing references.

Excel INDIRECT Function

Syntax

The syntax of the INDIRECT function is as follows.

=INDIRECT(ref_text, [a1])

Arguments:

The INDIRECT function accepts two arguments, with one being mandatory. The details of each are mentioned below.

'ref_text' – This is a mandatory argument that accepts the value of the cell reference as text. The input value can also be a reference which is named.

'a1' – This is an optional argument that indicates the type of reference included in the argument ref_text. As it is an optional argument, the default value of the argument a1 is TRUE which refers to an A1-style cell reference. If the value of the argument is FALSE, the R1C1-style cell reference is taken.

Important Characteristics of the INDIRECT Function

Some of the notable features of the INDIRECT function are outlined below.

  • If the value of the ref_text argument is not a valid cell reference, the INDIRECT throws the #REF! error.
  • The INDIRECT function is the easiest function to refer to another workbook. In such cases, if the referenced workbook is not open, the function returns the #REF! error. It is also important to enclose the worksheet or workbook names in single quotes if they contain spaces.
  • If the cell range mentioned in the ref_text argument is outside the row limit or column limit of Excel worksheets, the function indicates it with a #REF! error.
  • When using R1C1 style cell reference in ref_text, if the value of the second argument is TRUE, the INDIRECT function returns a #REF! error.

A few additional considerations while using the INDIRECT function is to avoid circular references as it may lead to circular dependency errors. Also, as the function is volatile, it recalculates whenever it detects a change in the worksheet, therefore when dealing with large datasets, it might cause performance problems.

Examples of INDIRECT Function

The INDIRECT function allows users to create dynamic formulas, hierarchical drop-down lists, and much more. Using the examples below, we will explore various applications of the function to improve data analysis and extraction.

Example 1 – Simple Use of INDIRECT Function

In the beginning, the INDIRECT function might appear confusing. To resolve that, in this example, we will take simple input values for both the arguments of the INDIRECT function to gain a better understanding of its functionality.

Simple Use of INDIRECT Function

In the first case, the input value of the ref_text is B2. The INDIRECT function navigates to cell B2 which contains the cell reference A2. The function then returns the value in cell A2 which is 10. Another way to use the INDIRECT function to retrieve the value in cell A2 is to use it as a cell reference in double quotes making it a text value, as illustrated in the second example.

The last example highlights the use of R1C1 style cell referencing where the INDIRECT function goes to cell B4 which contains the cell reference to cell A4 in R1C1 style. It is important to note that the value of the second argument is set to FALSE indicating the cell referencing style.

Perhaps you are thinking that simply entering =A2 in a cell would have gotten the value 10 for us anyway, you have figured correctly. But if we tell you that it's not the "10" we are after, it is specifically whatever value lies in the cell A2, things change a little. Suppose a column or row is added before or above A2, this "10" will shift and A2 will have a different value. =A2 will automatically become =B2 (if a column is added) or =A3 (for an added row). Incorporating the INDIRECT function, it will focus on just A2. We will demonstrate this further with an example later, but you may have gotten the idea of why INDIRECT can be important.

Hopefully, now you have demystified its usage and understand its capabilities more effectively. In the further examples, we will explore more interesting and practical applications of the INDIRECT function, that you can use on a daily basis.

Example 2 – Creating Dynamic Named Cell References

Imagine you are a teacher at a school, and you have compiled academic performance for different subjects in Excel. Marks obtained by each student are stored in each column for different subjects. Now, to calculate the total marks scored by each student, one way is to manually enter the cell range for each student separately. So, the total marks obtained by Emily can be calculated using the formula.

=SUM(B2:B6)
Creating Dynamic Named Cell References

To easily change the student's name, we will enter the cell range in cell B8 for each student and then use the INDIRECT function. The formula used will be as follows.

=SUM(INDIRECT(B8))
Creating Dynamic Named Cell References

Now I can just change the cell range in cell B8 and get the total marks for every student. Although it is easier than changing the cell range in the SUM formula, it is still not the most efficient way. We can go a step ahead and create named ranges for each student and then change the INDIRECT function to refer to those ranges.

To create the named ranges, we can use the built-in functionality of Excel (Formulas tab > Create from Selection). After selecting the cell range B1:G6 and choosing the top row as the naming criteria, we have the named ranges for all the students.

Creating Dynamic Named Cell References

Now we can simply replace the cell ranges with the named ranges.

Creating Dynamic Named Cell References

To make it more convenient, we can add a Data Validation list in cell B8.

Creating Dynamic Named Cell References

In this case, all the data was in the same sheet. What happens when each student's data is stored in a separate worksheet? In the next example, let's explore how to use the INDIRECT function to create dynamic references to a worksheet.

Example 3 – Creating Dynamic Worksheet Reference with INDIRECT Function

In this scenario, the same students' data is now stored in separate worksheets. The data includes marks obtained in different subjects, total marks, and grade obtained.

Creating Dynamic Worksheet Reference with INDIRECT Function

Each worksheet is named after the student's name making it easier to recognize.

Creating Dynamic Worksheet Reference with INDIRECT Function

Now, in the Scorecard sheet, we wish to extract information such as total marks scored, and the highest marks obtained by each student. The formula used will be as follows.

=SUM(Emily!C2:C6)
=MAX(Emily!C2:C6)
Creating Dynamic Worksheet Reference with INDIRECT Function

The idea is to construct a text string that represents the complete reference and then utilize the INDIRECT function to convert that textual representation into an actual reference.

Now as we can see, the format used is as follows.

=INDIRECT(sheetname!cell range)

As Excel automatically adds single quotes around the sheet name if it contains spaces, we must concatenate the sheet name with a single quote at the start and a single quote plus an exclamation mark at the end. We will also enclose the cell reference in double quotes as we are building a text string. So, the format used will be as follows.

=INDIRECT("'"&sheetname&"'!"&"cell range")

Let's take cell C4 where we can enter the student's name. The formula is as follows.

=SUM(INDIRECT("'"&$C$4&"'!"&"C2:C6"))
=MAX(INDIRECT("'"&$C$4&"'!"&"C2:C6"))
Creating Dynamic Worksheet Reference with INDIRECT Function

Now instead of manually changing the worksheet reference, we just need to enter the student's name (worksheet name) in cell C4, and we will get the desired data.

Now you know how to create dynamic cell references and references of different worksheets using the INDIRECT function.

Example 4 – Creating Fixed Ranges with INDIRECT Function

Suppose you maintain daily sales data in Excel. The dataset contains every sale that occurs on a day along with the product name. Using this data, we can easily calculate total sales to date. The easiest and most straightforward way is to use the SUM function, where the formula will be as follows.

=SUM(C2:C10)
Creating Fixed Ranges with INDIRECT Function

Now, upon rechecking, we realized that there is a missing entry for the first day, as the sheet was not set up until that date. We can easily insert a row on the top. Upon doing that, Excel automatically accommodated the changes in the formula, bringing it to the following.

=SUM(C3:C11)
Creating Fixed Ranges with INDIRECT Function

This will skip the top entry and we will not get the correct sum. To ensure that Excel maintains a specific cell range irrespective of such changes, we can use the INDIRECT function to create dynamic cell references that remain fixed. The formula used will be as follows.

=SUM(INDIRECT("C2"):C10)

Here Excel considers "C2" as a string and therefore does not change even when there are updates in the dataset. By using INDIRECT, we create a fixed cell reference for "C2," while the end cell range C10 can still adjust automatically based on any changes to the dataset. We'll apply the formula to the original dataset without the additional entry.

Creating Fixed Ranges with INDIRECT Function

Let's check what happens when we insert a new row in the beginning. The formula automatically gets updated to the following.

=SUM(INDIRECT("C2"):C10)
Creating Fixed Ranges with INDIRECT Function

A simple comparison highlights the use of the INDIRECT function where this dynamic approach allows the formula to adapt to new data while maintaining the desired starting point defined by the fixed cell reference.

Example 5 – Using INDIRECT Function with R1C1 Referencing

In this scenario, we have the sales data spreadsheet which contains department-wise sales for each day. At the end of each day, all the sales are added in a new column. The aim is to retrieve the total sales of the latest day without manually entering the last column's cell reference.

Using INDIRECT Function with R1C1 Referencing

Using the INDIRECT function with R1C1 referencing, we can dynamically fetch the latest sales. We require the row number and the column number to extract the data. We already know that the row number remains constant which is R7 (total sales) whereas the column number varies as a new column is added each day.

To calculate the column number dynamically, we can use the COUNTA function. It will count the number of non-empty cells in row 7 which will translate to the last column number. The formula used will be as follows.

=COUNTA(7:7)
Using INDIRECT Function with R1C1 Referencing

Now, the INDIRECT formula to retrieve the last value of the last column will be as follows.

=INDIRECT("R7C"&COUNTA(7:7),FALSE)

The input value of the second argument of the INDIRECT function is set to FALSE indicating that the type of reference used is R1C1 style. R7C refers to row 7 while the column reference is determined by the COUNTA function. The column reference is concatenated to R7C using the & operator.

Using INDIRECT Function with R1C1 Referencing

Now when a column is added for the next day, let's see if the formula returns the correct value.

Using INDIRECT Function with R1C1 Referencing

We have the correct value. With the help of the INDIRECT function in R1C1 style, we can dynamically update the last column even if more days are added to the sales data table.

Try and use this logic when the data is in a different worksheet.

Example 6 – Using INDIRECT with Data Validation in Excel

Another excellent use of the INDIRECT function is to create multi-level dependent drop-down lists through data validation. Suppose we have a dataset containing team names for an upcoming sports event along with team members in each team.

Using INDIRECT with Data Validation in Excel

Now we wish to create a drop-down list to first choose the team name and then dynamically choose the team member name. The first step is to create named ranges for all columns. We can do this either by adding a new name in the Name Manager (Formulas tab > Name Manager > New) or typing the name directly in the Name Box.

Using INDIRECT with Data Validation in Excel

To create the first drop-down list, we can navigate to the Data tab and select Data Validation. Now choose List under Allow and enter the range name in the Source box as follows.

=Team_Name
Using INDIRECT with Data Validation in Excel

Now we have a drop-down list that includes all the data in the named range 'Team_Name'.

Using INDIRECT with Data Validation in Excel

Now, to create a dependent drop-down, repeat the process to create a data validation; however, instead of the range name, enter the following formula.

=INDIRECT($A$10)

It is important to use the cell reference that contains the first drop-down list. It will dynamically fetch the data from the range referenced in cell A10 and populate the dependent drop-down menu accordingly.

Using INDIRECT with Data Validation in Excel

So, this process allows users to select a 'Team Name' from the first drop-down list and then choose 'Team Members' from the second list which dynamically changes the options available based on the selection made in the previous one. Now, try to create another drop-down list based on the second input.

INDIRECT Function with VLOOKUP Function

Suppose you have a dataset that represents sales of six store locations for specific months. As such datasets are large, we wish to dynamically look up the total sales for a specific product from a selected month.

INDIRECT Function with VLOOKUP Function

One way is to use the VLOOKUP function with a nested IF function. The logic used will be if you require January sales, the formula will refer to the "Jan_sales" dataset, or if it's February sales, refer to "Feb_sales", and so on. It is clearly not the most efficient way to extract data, especially when dealing with more than two datasets.

The best approach would be to use a combination of INDIRECT and VLOOKUP functions. To make things simpler, we will first use named ranges for all the given datasets. So, cell range !$A$3:$B$8 is named 'Jan_Sales', and a similar pattern is followed for all other months.

Our next step involves creating a summary table that offers users the flexibility to select both the month and store location of interest. By doing so, they can obtain the total sales for the specific combination they choose.

INDIRECT Function with VLOOKUP Function

So, in this case, Cell B10 will store the user input for 'Store Location' and B11 contains the value of 'Month'.

To create a dynamic referencing for datasets, the formula used will be as follows. As the naming convention of the datasets is fixed (Month_Sales), the user can just enter the month reference and the function returns the relevant dataset. Here, we concatenate the input month with the fixed suffix naming convention.

=INDIRECT(B11&"_Sales")
INDIRECT Function with VLOOKUP Function

Now we can use the VLOOKUP function to look for total sales of the value in cell B10 (Store Location) from the dataset returned by the INDIRECT function. The formula used will be as follows.

=VLOOKUP(B10,INDIRECT(B11&"_Sales"),2,FALSE)

With this formula, we are looking up the entered store location in B10. The lookup array is defined by the dataset extracted by the INDIRECT function for the month mentioned in B11. 2 in the formula is the column number (of the extracted dataset) the value is to be returned from. FALSE indicates that VLOOKUP is to search for an exact match.

INDIRECT Function with VLOOKUP Function

To make things more efficient, we can use Data Validation to create a list to choose Store Location.

INDIRECT Function with VLOOKUP FunctionIndeed, the combination of the INDIRECT and VLOOKUP functions empowers dynamic lookups based on user-provided inputs. This flexibility allows for efficient data analysis and summary based on the user's specific requirements. Using the same logic, try to retrieve data when the same data is spread across separate worksheets and the worksheet names align with the dataset names.

Now that you have added the INDIRECT function to your Excel arsenal, create dynamic and interactive spreadsheets. Practice and explore more applications, while we bring to you another useful Excel function.

About Shubhra Jain

Meet Shubhra Anand Jain, a dedicated Excel enthusiast with over 5 years of expertise, specializing in data analysis, complex formula development, and automation. Based in Sweden, she's on a mission to simplify Excel, one formula at a time. Check out Shubhra's extended profile here.