Indirect formula in excel is an inbuilt function which is used to reference and get cell values from a text string, this formula takes reference from the referred cell, it has two arguments the first argument is not optional while the second argument is optional which refers to the type of match, this function can also be used with another formula.
INDIRECT Formula in Excel – Table of Contents
What is INDIRECT Formula in Excel?
Indirect Formula is basically referencing to another cell in excel. It is a very useful formula in excel which uses a text string as a reference to find a value for that text string. Let us learn how to use an indirect formula in excel in this topic.
Syntax of INDIRECT Formula in Excel
Ref_text is a reference given to any specific cell while the styles can be different. The indirect formula can be used to reference named ranges or even referring to another worksheet. Basically, it takes references from a cell which has text string for its value.
From the syntax we saw above indirect formula uses two arguments one is the reference text and another one is the style of reference. There are two types of cell reference in excel.
- A1 style: This is basically the commonly used style in excel like cell A1 or cell A2.
- R1C1 Style: This style is called as referring to rows and columns style.
We will learn about those referencing by various examples.
How to Use INDIRECT Formula in Excel?
Below are the examples of INDIRECT Formula in excel.
Example #1
First we will learn the use of indirect formula by referencing to named ranges.
Named Ranges are the name given to columns for reference. Consider the following data.
To make a column name range select the data and give a name from the far left column in the excel worksheet. For example, select cells B2: B5 and name it as Anand in the name box. As shown in the screenshot below.
Repeat the process for every column. Now we will calculate the total marks obtained by Aran by using indirect formula.
Step 1: In Cell H2 type the following formula,
Step 2: If we simply use the function on cell G2 the value will be absurd as it is a string function, So nest another formula inside the sum excel function which is an indirect formula.
Step 3: Now select cell G2 and close the parenthesis and press enter.
4.9 (1,353 ratings)
Step 4: When we press enter we see that the sum of marks obtained by Aran Is calculated.
Step 5: Now let us change the string in cell G2 as Anand and see the result.
What indirect excel formula is doing here is it takes String mentioned in cell H2 and refers to the value associated with the string. Now the string mentioned in the cell is a named range so it refers to the value associated with the name range.
The above example was to demonstrate how Indirect excel formula works with named ranges.
Example #2
Now let us use another example to demonstrate the use of indirect excel function while referring to different worksheets. We can use the same data as taken in example 1.
To try this method, we need to rename the worksheet a specific name. For the current scenario I have named my worksheet as marks. Suppose we want marks of any specific student in worksheet 2.
In the current example, I want to find out the marks of Aran in another worksheet.
Step 1: IN cell B2, write the sum formula,
Step 2: Use an indirect function for referencing,
Step 3: Now here is a little trick involved in this, we need to select the reference text and concatenate with the value we are trying to solve. Write the following formula.
Step 4: When we press enter we see the sum of marks for the student Aran Is calculated.
Step 5: Now if we change the name to Anand and see what the value is being generated.
Step 6: Press Enter to see the result.
The above example is to demonstrate the use of indirect excel formula while referencing to another worksheet. Also, one thing to remember is we also referred to name ranges too in this example simultaneously. These both can be done together also. But the above two examples were for A1 styles which is the most commonly used style in Excel.
Example #3
In this example, we will learn how to use R1C1 style for indirect function. First, let us understand what this referencing style means in actual. R stands for rows and C stands for columns. Suppose I have to refer to cell B2, in R1C1 style the reference would be R2C2 means row 2 and column 2 which is B2 in normal referencing styles.
For this example, I have sales done by employees in various months. Consider the data below.
Step 1: Suppose I want the total of sales done in March in any cell, Type the following formula,
Step 2: Sum of March is in D7 which is sixth row and 4th column so type in inverted commas R7C4.
Step 3: Now after inserting a comma type or click on False for R1C1 Style.
Step 4: Press Enter and see the result.
The value for the sum of march sales done by employees is returned in the cell F2 but by using R1C1 Style.
Things to Remember
There are few things which we need to remember about the indirect formula in excel:
- It has two referencing styles: A1 style and R1C1 Style (true stands for A1 style while False stands for R1C1 Style).
- The INDIRECT function can be used in both named ranged and referring to different worksheets together.
- If we are using an INDIRECT function to refer to different worksheet we need to keep in mind about the spaces in the name of the worksheet.
Recommended Articles-
This has been a guide to INDIRECT Formula in Excel. Here we discuss how to use INDIRECT Formula in Excel with examples and downloadable excel template. You may learn more about excel from the following articles –
- Comma Style in Excel
- Excel Rows and Columns
- How to Concatenate Two Columns in Excel?
- VBA String Functions
- VBA DIR Function
- False Excel Function
- Convert Rows to Columns in Excel
- Top Shortcuts in Excel
- Using Dynamic Named Range Excel
- Apply LOOKUP Excel Function
- 35+ Courses
- 120+ Hours
- Full Lifetime Access
- Certificate of Completion
