Contents
Excel INDIRECT Function
![Excel-INDIRECT-Function Excel-INDIRECT-Function](https://excelguruji.in/wp-content/uploads/2023/12/Excel-INDIRECT-Function.jpg)
When to Use the Excel INDIRECT Function in Excel
The INDIRECT function1 in Excel proves invaluable when you need to dynamically reference cells or ranges. It allows you to create flexible and dynamic formulas by converting text representations of cell references or named ranges into actual references.
What Excel INDIRECT Function Returns
The Excel INDIRECT function returns the value of the referenced cell or range specified by a text string. This enables you to construct formulas that can change dynamically based on the contents of a cell.
Syntax of Excel INDIRECT Function
The syntax of the Excel INDIRECT function is as follows:
=INDIRECT(ref_text, [a1])
Input Arguments
Here are the input arguments for the Excel INDIRECT function:
● ref_text: This is the reference text that you want to convert into an actual cell reference or a named range. It can be a text string enclosed in quotation marks, a cell reference, or the result of another formula.
● [a1]: This is an optional argument that determines the reference type. If omitted or set to TRUE, the reference is interpreted in A1 style (column letter and row number). If set to FALSE, the reference is interpreted in R1C1 style (row and column number).
![Excel-INDIRECT-Function-Syntax Excel INDIRECT Function](https://excelguruji.in/wp-content/uploads/2023/12/Excel-INDIRECT-Function-Syntax.jpg)
Extra Notes
● If the ref_text argument refers to a cell on another sheet, that sheet must be open, or the INDIRECT function will return a #REF! error.
● Excel INDIRECT function is a volatile functions (functions that recalculate with every change in the worksheet). This lead to performance issues and slow down your workbook.
● The ref_text argument can include the sheet name followed by an exclamation mark to specify a cell or range on a different sheet.
Examples of the Excel INDIRECT Function
Here are few examples of using the Excel INDIRECR function in Excel:
Example 1: Basic Usage
Suppose you have data in cell A1, and you want to retrieve the value from that cell using the INDIRECT function. You just need to provide it with the name of the cell, written as text, as shown in the example below.
![Excel-INDIRECT-Function-Example-1 Excel-INDIRECT-Function-Example-1](https://excelguruji.in/wp-content/uploads/2023/12/Excel-INDIRECT-Function-Example-1.jpg)
Here is an explanation of the formula: =INDIRECT(“A1”)
The formula above accepts the cell reference A1 as an input argument (enclosed in double quotes as a text string) and yields the value found in that cell, which, in this case, is 100.
Now, you might be wondering if this could have been achieved by directly referencing the sales (e.g., A1 or =$A$1), as demonstrated below. Then, why should we use the INDIRECT function unnecessarily?
![Excel-INDIRECT-Function-Example-2 Excel-INDIRECT-Function-Example-2](https://excelguruji.in/wp-content/uploads/2023/12/Excel-INDIRECT-Function-Example-2.jpg)
When you use =A1 or =$A$1, you get the same result. But, if you add a row above the first row, the cell references change automatically to fit the new row.
![Excel-INDIRECT-Function-Example-3 Excel-INDIRECT-Function-Example-3](https://excelguruji.in/wp-content/uploads/2023/12/Excel-INDIRECT-Function-Example-3.jpg)
When adding rows or columns to a worksheet and needing to keep the cell reference locked and unchanged, you can utilize the Excel INDIRECT function.
Example 2: Use Cell Reference
Suppose you have data in cell A1, and you want to dynamically reference the cell specified in cell C1. In cell E1, you can use the following formula:
![Excel-INDIRECT-Function-Example-4 Excel-INDIRECT-Function-Example-4](https://excelguruji.in/wp-content/uploads/2023/12/Excel-INDIRECT-Function-Example-4.jpg)
When you use the Excel INDIRECT function with C1 (which holds a cell address as text), it makes A1 a proper reference. This means it looks at A1 and gives you what is there.
Remember, no need for quotes because C1 has the cell address in text.
But if C1 has something that is not a proper cell address, INDIRECT will show an error (#REF!).
You can make a reference to a cell by using both the column letter and the row number. For example, if you have the number 1 in cell C1, and you use the formula =INDIRECT(“A” & C1), it will point to cell A1, as shown in the example below.
![Excel-INDIRECT-Function-Example-5 Excel-INDIRECT-Function-Example-5](https://excelguruji.in/wp-content/uploads/2023/12/Excel-INDIRECT-Function-Example-5.jpg)
Example 3: Calculate the Average of a Range of Cells
You can use the Excel INDIRECT function in Excel to reference a range of cells, just as you would reference a single cell, as shown in the example below.
![Excel-INDIRECT-Function-Example-6 Excel-INDIRECT-Function-Example-6](https://excelguruji.in/wp-content/uploads/2023/12/Excel-INDIRECT-Function-Example-6.jpg)
Here is an explanation of the formula: =AVERAGE(INDIRECT(“A1:A6”))
- INDIRECT(“A1:A6”): The INDIRECT function is used to convert the text string “A1:A6” into a reference to the actual range A1 to A6. So, it essentially refers to the range of cells A1 to A6.
- AVERAGE(INDIRECT(“A1:A6”)): The AVERAGE function then calculates the average of the numbers in the range A1 to A6.
Just like the AVERAGE function, you can use other functions such as SUM, COUNT, MAX, MIN, LARGE, SMALL, etc.
Example 4: INDIRECT Function With Name Range
When Excel INDIRECT function combined with named ranges, it can be a powerful tool for creating dynamic and flexible formulas. Named ranges allow you to assign a meaningful name to a specific cell or range of cells, making it easier to understand and manage your formulas.
Suppose you have the following data on test scores for students in different subjects:
![Excel-INDIRECT-Function-Example-7-1 Excel INDIRECT Function Example 7 1](https://excelguruji.in/wp-content/uploads/2023/12/Excel-INDIRECT-Function-Example-7-1.jpg)
The scores for the subjects are listed in columns B, C, and D. To make it easier to refer to these ranges, you can assign names to them. Specifically, you want to name the range in column B as “Math” the range in column C as “Science”, and the range in column D as “English”.
Now, to achieve this, you can follow the method below:
Select the Range:
- Highlight the cells in column B where the Math scores are (e.g., B2 to B7).
- Then, go to the “Formulas” tab on the ribbon.
- Click on “Define Name” or “Name Manager” (depending on your Excel version).
- Enter the name “Math” and click OK. Now, the range B2:B7 is named as “Maths”.
- Repeat the same process for Other Subjects:
![Indirect-Function-1 Indirect-Function-1](https://excelguruji.in/wp-content/uploads/2023/12/Indirect-Function-1.gif)
Now, you can use the Excel INDIRECT function to refer to the named range in a formula.
For example, if you want to average the values in the named range “Math” you could use the following formula:
![Excel-INDIRECT-Function-Example-8 Excel-INDIRECT-Function-Example-8](https://excelguruji.in/wp-content/uploads/2023/12/Excel-INDIRECT-Function-Example-8.jpg)
Example 5: Dependent Drop-down List with the Excel INDIRECT Function
Creating a dependent dropdown list in Excel using the INDIRECT function involves setting up named ranges and using data validation.
For example, suppose you have a list of categories arranged in a row and their corresponding subcategories in a column, as shown below:
![Excel-INDIRECT-Function-Example-9 Excel-INDIRECT-Function-Example-9](https://excelguruji.in/wp-content/uploads/2023/12/Excel-INDIRECT-Function-Example-9.jpg)
Now, to create a dependent dropdown list, assign a name range to each category, such as A2:A6 for Electronics, B2:B6 for Clothing, and C2:C6 for Furniture. We have already covered how to assign name ranges in the previous example.
Now, select the cell where you want to create a dropdown list for the main category (Electronics, Clothing, Furniture). This will be the first drop-down list.
- Select cell E2
- Click on Data Tab
- Click on Data Validation
- Select List as the Validation criteria and use the following formula in the Source field: =$A$1:$C$1
![Excel-INDIRECT-Function-Example-10-1 Excel-INDIRECT-Function-Example-10-1](https://excelguruji.in/wp-content/uploads/2023/12/Excel-INDIRECT-Function-Example-10-1.jpg)
Your first drop-down list will look like below:
![Excel-INDIRECT-Function-Example-11 Excel-INDIRECT-Function-Example-11](https://excelguruji.in/wp-content/uploads/2023/12/Excel-INDIRECT-Function-Example-11.jpg)
Now follow below method to create dependable drop-down list:
- Select cell F2
- Click on Data Tab
- Click on Data Validation
- Select List as the Validation criteria and use the following formula in the Source field: =$A$1:$C$1
![Excel-INDIRECT-Function-Example-12 Excel-INDIRECT-Function-Example-12](https://excelguruji.in/wp-content/uploads/2023/12/Excel-INDIRECT-Function-Example-12.jpg)
Now, when you select ‘Electronics’ in the first drop-down (cell E2), the dependent drop-down in cell F2 will display the subcategories of Electronics (Phones, Laptops, Headphones, Smartwatches, Digital Cameras).
![Excel-INDIRECT-Function-Example-13 Excel INDIRECT Function Example 13](https://excelguruji.in/wp-content/uploads/2023/12/Excel-INDIRECT-Function-Example-13.jpg)
And when you select ‘Furniture’ in the first drop-down, the dependent drop-down in cell F2 will display the subcategories of Furniture (Tables, Sofas, Chairs, Beds, Dining Sets).
Practice Workbook
Download Free Excel File For PracticeConclusion
In conclusion, the Excel INDIRECT function is a powerful tool for creating dynamic and flexible formulas. It enables you to build formulas that adapt to changes in your spreadsheet, providing a level of versatility that static cell references cannot achieve.
As with any Excel function, it is essential to understand the nuances and potential pitfalls of the INDIRECT function. Experiment with different applications to enhance your proficiency and efficiency in utilizing this function in your Excel projects.
Frequently Asked Questions (FAQs)
Q1: Can the INDIRECT function reference cells on other sheets?
A1: Yes, by including the sheet name followed by an exclamation mark in the ref_text argument.
Q2: What happens if the referenced sheet is not open?
A2: If the referenced sheet is not open, the INDIRECT function will return a #REF! error.
Q3: What is the purpose of the [a1] argument?
A3: The [a1] argument determines the reference type. If TRUE or omitted, the reference is interpreted in A1 style; if FALSE, in R1C1 style.
Q4: Is there a performance impact when using the INDIRECT function with volatile functions?
A4: Yes, using the INDIRECT function with volatile functions can lead to performance issues.
Other Related Excel Functions
Mastering Excel VLOOKUP Function: A Comprehensive Guide to Enhance Your Data Analysis Skills
Mastering Excel FILTER Function: A Comprehensive Guide for Data Filtering
Join me on Instagram, YouTube and WhatApp Channel for your daily dose of valuable tips and tricks! Catch insightful videos that will enhance your knowledge and skills. Don’t miss out – follow me now!