• Skip to primary navigation
  • Skip to main content
  • Skip to footer
WallStreetMojo

Wallstreet Mojo

Wallstreet Mojo

MENUMENU
  • Resources
        • Excel

          • Excel Functions
          • Excel Tools
          • Excel Tips
        • Excel
        • Financial Functions Excel

          • NPV in Excel
          • IRR in excel
          • PV in Excel
        • Financial-Functions-Excel
        • Lookup Functions Excel

          • VLOOKUP
          • HLOOKUP
          • Index Function
        • Lookup-Functions-in-Excel
        • Excel Charts

          • Pareto Chart in Excel
          • Gannt Chart in Excel
          • Waterfall Chart in Excel
        • Excel-Charts
        • VBA

          • VBA Left Function
          • VBA Paste Special
          • VBA Worksheet Function
        • VBA
        • Others

          • Resources (A to Z)
          • Financial Modeling
          • Equity Research
          • Private Equity
          • Corporate Finance
          • Financial Statement Analysis
  • Free Courses
  • All Courses
        • Certification Courses

          Excel Course certificate
        • Excel VBA All in One Bundle

          Excel-VBA-Certification-Course
        • Excel Data Analysis Course

          Excel-Data-Analysis-Course
        • VBA Macros Course

          VBA-Training-Course
        • Others

          • Basic Excel Training
          • Advanced Excel Course
          • Tableau Certification Course
          • Excel for Finance Course

          • Excel for Marketers Course
          • Excel for HR Managers
          • Excel for Power Users
          • View All
  • Excel VBA All in One Bundle
  • Login

Indirect Formula in Excel

Home » Excel » Lookup & Reference Functions in Excel » Indirect Formula in Excel

By Tejswini Bhosale Leave a Comment

indirect formula in excel

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

  • Excel INDIRECT Formula
  • How to Use?

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

indirect formula excel1

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.

You can download this INDIRECT Formula Excel Template here – INDIRECT Formula Excel Template

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.

indirect formula excel example 1.1

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.

indirect formula excel example 1.2

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,

indirect formula excel example 1.3

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.

indirect formula excel example 1.4

Step 3: Now select cell G2 and close the parenthesis and press enter.

Popular Course in this category
Cyber Monday Sale
All in One Excel VBA Bundle (35 Courses with Projects) 35+ Courses | 120+ Hours | Full Lifetime Access | Certificate of Completion
4.9 (1,353 ratings)
Course Price

View Course

Related Courses
VBA Macros CourseAdvanced Excel CourseTableau Certification Course

indirect formula excel example 1.5

Step 4: When we press enter we see that the sum of marks obtained by Aran Is calculated.

indirect formula excel example 1.6

Step 5: Now let us change the string in cell G2 as Anand and see the result.

indirect formula excel example 1.7

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.

indirect formula excel example 2.1

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,

indirect formula excel example 2.2

Step 2: Use an indirect function for referencing,

Example 2.3

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.

Example 2.4

Step 4: When we press enter we see the sum of marks for the student Aran Is calculated.

Example 2.5

Step 5: Now if we change the name to Anand and see what the value is being generated.

Example 2.7

Step 6: Press Enter to see the result.

Sum Indirect

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.

Example 3.1

Step 1: Suppose I want the total of sales done in March in any cell, Type the following formula,

Example 3.2

Step 2:  Sum of March is in D7 which is sixth row and 4th column so type in inverted commas R7C4.

Example 3.3

Step 3: Now after inserting a comma type or click on False for R1C1 Style.

Example 3.4

Step 4: Press Enter and see the result.

Example 3.5

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:

  1. It has two referencing styles: A1 style and R1C1 Style (true stands for A1 style while False stands for R1C1 Style).
  2. The INDIRECT function can be used in both named ranged and referring to different worksheets together.
  3. 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
0 Shares
Share
Tweet
Share
All in One Excel VBA Bundle (35 Courses with Projects)
  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>

Filed Under: Excel, Lookup & Reference Functions in Excel

Reader Interactions
Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Footer
COMPANY
About
Reviews
Blog
Contact
Privacy
Terms of Service
FREE COURSES
Free Finance Online Course
Free Accounting Online Course
Free Online Excel Course
Free VBA Course
Free Investment Banking Course
Free Financial Modeling Course
Free Ratio Analysis Course

CERTIFICATION COURSES
All Courses
Financial Analyst All in One Course
Investment Banking Course
Financial Modeling Course
Private Equity Course
Business Valuation Course
Equity Research Course
CFA Level 1 Course
CFA Level 2 Course
Venture Capital Course
Microsoft Excel Course
VBA Macros Course
Accounting Course
Advanced Excel Course
Fixed Income Course
RESOURCES
Investment Banking
Financial Modeling
Equity Research
Private Equity
Excel
Books
Certifications
Accounting
Asset Management
Risk Management

Copyright © 2019. CFA Institute Does Not Endorse, Promote, Or Warrant The Accuracy Or Quality Of WallStreetMojo. CFA® And Chartered Financial Analyst® Are Registered Trademarks Owned By CFA Institute.
Return to top

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
WallStreetMojo

Free Investment Banking Course

IB Excel Templates, Accounting, Valuation, Financial Modeling, Video Tutorials

By continuing above step, you agree to our Terms of Use and Privacy Policy.

* Please provide your correct email id. Login details for this Free course will be emailed to you

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.

* Please provide your correct email id. Login details for this Free course will be emailed to you

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.

* Please provide your correct email id. Login details for this Free course will be emailed to you

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.

* Please provide your correct email id. Login details for this Free course will be emailed to you

WallStreetMojo

Download INDIRECT Formula Excel Template

By continuing above step, you agree to our Terms of Use and Privacy Policy.

Limited Period Offer - All in One Excel VBA Bundle (35 Courses with Projects) View More