• Skip to primary navigation
  • Skip to content
  • Skip to primary sidebar
  • Skip to footer
EDUCBA

EDUCBA

MENUMENU
  • Resources
        • Excel Charts

          • Histogram Chart Excel
          • Basic Excel Formulas
          • Text to Columns in Excel
        • Excel Charts
        • Excel Tips

          • Excel Gantt Chart
          • IFERROR with VLOOKUP
          • Data Table in Excel
        • Excel Tips
        • Excel Tools in Excel

          • Stacked Column Chart
          • Cheat Sheet of Excel Formulas
          • Excel Data Validation
        • Histogram chart in excel
        • Others

          • Resources (A-Z)
          • Excel Functions
          • Financial Functions in Excel
          • Logical Functions in Excel
          • Lookup Reference Functions in Excel
          • Maths Function in Excel
          • TEXT and String Functions in Excel
          • View All
  • Free Courses
  • All Courses
        • Certification Courses

          Excel Course 1
        • All in One Bundle

          All-in-One-Excel-VBA-Bundle
        • Excel course

          Excel-Training
        • Others

          • Excel advanced course
          • VBA Course
          • Excel Data Analysis Course
          • Excel for Marketing Course
          • Excel for Finance Course
          • View All
  • 120+ Courses All in One Bundle
  • Login

INDIRECT Function in Excel

Home » Excel » Blog » Lookup & Reference Functions in Excel » INDIRECT Function in Excel

 INDIRECT Function

Excel INDIRECT Function (Table of contents)

  • INDIRECT Function in Excel
  • INDIRECT Formula in Excel
  • How to use the INDIRECT Function in Excel?

INDIRECT Function in Excel

An INDIRECT function is not a regular user of a formula for us. It is one of the cool hidden tricks, which can enhance your productivity level.

Start Your Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

As the name itself suggests it is used to indirectly reference a cell in a worksheet function.

INDIRECT Formula in Excel

Indirect formula 1

An INDIRECT function consists of two arguments. One is Ref_Text that is a mandatory argument and the second one is [a1].

  • Ref_Text: It is a reference to a cell contains either An A1-style reference or An R1C1-Style Reference or A name defined by the name/formula or A reference to a cell in text form.  This argument can reference to another workbook.
  • [a1]: It is an optional argument. This argument is consists of two logical values, TRUE or FALSE. Whatever the value we got in Ref_Text show as A1-Style Reference if it is TRUE and shows as R1C1 Style Reference if it is FALSE.

Indirect formula 2

Note: If the last argument if omitted by default excel will take TRUE as the argument.

How to Use the INDIRECT Function in Excel?

An INDIRECT function in Excel is very simple easy to use. Let us now see how to use INDIRECT Function in Excel with the help of some examples.

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

Example #1 – INDIRECT Function with Named Ranges

Below table shows the sales amount of different sales managers across 4 regions. In cell H4, we need to get the total of the region, which is mentioned in the cell G4.

Sales amount

Combination of INDIRECT & Named Ranges is invaluable in this kind of context. Named Range will define the range of cells in a single name.

Popular Course in this category
Cyber Week Sale
Excel Training (18 Courses, 9+ Projects) 18 Online Courses | 9 Hands-on Projects | 95+ Hours | Verifiable Certificate of Completion | Lifetime Access
4.9 (2,629 ratings)
Course Price

View Course

Related Courses
Excel Advanced Training (14 Courses, 21+ Projects)

Step 1: Create a named range for each region. Go to Formula tab and then click on Name Manager.

Create named range

  • New Name dialog box appears. Give the name as East.

New Name dialog box

Note: Repeat this task for each region. Give the region name to each name manager.

Give the region name

Step 2: Now apply INDIRECT function along with SUM function to get the total in cell H4.

Result of INDIRECT Function 1

Note: Change the Region name in cell G4 to get the total of each region.

Pro Tip: Create a drop-down list for Regions.

Example #2 – INDIRECT Function with Average Function

Use the previous sales amount data of different sales managers across 4 regions. In cell H7, we need to get the average total of the region, which is mentioned in the cell G7.

INDIRECT Function with Average Function

Now apply INDIRECT function along with an AVERAGE function to get the total in cell H4.

Result of INDIRECT Function 2

Example #3 – INDIRECT Function to return the last cell value

With the help of below data, we need to find the Profit value of the last Item. In cell H2 last item’s profit.

INDIRECT Function to return the last cell value

In this example, we will look into the R1C1 style reference with INDIRECT to return the last cell value. Here we used COUNTA function to get the last column number.

COUNTA function

Result of INDIRECT Function 3

In the above formula, we have concatenated R10 (Row 10) with C10 by using COUNTA function. COUNTA will return the value 5 (last column number). Combination of both these will give “R10C5” i.e. Indirect will the value from Row 10 and Column 5.

FALSE part of the argument will request for an R1C1 style of the cell reference.

As the Item numbers keep increases this function will always give the last item’s profit value.

Example #4 – INDIRECT + Named Range + VLookup

Combination of Indirect with Named Range & Vlookup will be a dynamic combination to have. It is necessary to include the example of Indirect with Vlookup.

In the table below, apply the VLOOKUP function in cell B15 to get the sales value based on the cells B13 & B14.

INDIRECT + Named Range + VLookup

Steps involved in before applying Vlookup.

  • Create a dropdown list for both region & sales manager list
  • Create 4 named ranges (refer example 1) for all the 4 regions. Please note apply named ranges including sales manager name.

indirect example 4.1

  • After the above two steps apply VLOOKUP function in cell B15.

Result of INDIRECT Function 4

Since we have defined range list with East_List, West_List, North_List, South_List what INDIRECT function will do is it will give a reference to that particular region list. Therefore, Vlookup will consider that as a table range and will give the result for the respective sales manager.

From the drop-down list, change the play with different region & different sales managers.

Example #5 – Refer Worksheet with INDIRECT.

An INDIRECT function can also retrieve the cell value from another worksheet. This example will give an explanation to this.

  • Indirect Sheet is the sheet we are entering a formula.
  • Summary Sheet is the sheet where data is there.
  • In Indirect Sheet enter the formula in cell C2.
  • You need to give a reference to a cell B7 in the Summary Sheet.

Below images will show different sheets in a single window.

Indirect vs summary

indirect example 5

Here we have concatenated Summary Sheet in cell A2 of Indirect Sheet & and taking a reference to B7 cell from the Summary Sheet in Indirect Sheet.

Note: INDIRECT Function is not the most often used formula. But could be a lethal weapon in extreme cases to save a lot of time and automate the cell value. Try to use regularly and you will get a hang on of it.

Things to Remember about INDIRECT Function

  • The reference must be a valid reference otherwise; function will return a #REF error.
  • When it is referring to the other workbook, the respective workbook should be in an open mode to get the results.
  • Generally, an INDIRECT function will return A1 style reference by default.
  • When we are using [a1] reference as FALSE, we need to put 0 and put 1 for TRUE.
  • In R1C1 style reference, you can formulas like COUNTA to dynamically give a reference to the row or column. If the data increases, it will be a dynamic function and need not change every now and then.

Recommended Articles

This has been a guide to INDIRECT. Here we discuss the INDIRECT Formula and how to use the INDIRECT function along with practical examples and downloadable excel templates. You may also have a look at these other lookup and reference functions in excel-

  1. How to Use COLUMN Function in Excel?
  2. CHOOSE Function in Excel
  3. Excel AND Function
  4. VBA excel programming

All in One Excel VBA Bundle (120+ Courses, 30+ Projects)

120+ Online Courses

30+ Projects

500+ Hours

Verifiable Certificates

Lifetime Access

Learn More

0 Shares
Share
Tweet
Share
Reader Interactions
Leave a Reply Cancel reply

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

Primary Sidebar
Excel Functions Tutorials
  • Lookup Reference Functions in Excel
    • ROWS Function in Excel
    • Excel INDEX Function
    • VLOOKUP Table Array
    • Excel OFFSET Formula
    • VLOOKUP For Text
    • IF VLOOKUP Formula in Excel
    • Mixed Reference in Excel
    • CHOOSE Formula in Excel
    • Excel COLUMN to Number
    • Excel Alternatives to VLOOKUP
    • HLOOKUP Examples
    • Excel VLOOKUP From Another Sheet
    • VLOOKUP with Sum
    • VBA VLOOKUP Function
    • Fixing VLOOKUP Errors
    • Excel ROW Function
    • HYPERLINK in Excel
    • Address Excel Function
    • Excel COLUMNS Function
    • Excel REPLACE Function
    • OFFSET Excel Function
    • Excel GETPIVOTDATA Function
    • MATCH Function in Excel
    • VLOOKUP Function in Excel
    • HLOOKUP Function in Excel
    • LOOKUP in Excel
    • CHOOSE Function in Excel
    • TRANSPOSE in Excel
    • COLUMN Function in Excel
    • INDIRECT Function in Excel
  • Excel Functions (10+)
  • Excel Tools (97+)
  • Financial Functions in Excel (17+)
  • Logical Functions in Excel (12+)
  • Maths Function in Excel (39+)
  • TEXT and String Functions in Excel (25+)
  • Date and Time Function in Excel (20+)
  • Statistical Functions in Excel (55+)
  • Information Functions in Excel (4+)
  • Excel Charts (44+)
  • Excel Tips (195+)
  • VBA (180+)
  • Workplace Productivity (4+)
  • Microsoft Office Tools (14+)
  • Excel Formula and Functions (20+)
  • MS Excel Shortcuts (4+)
Excel Functions Courses
  • Online EXCEL COURSE
  • Online EXCEL ADVANCED Training
Footer
About Us
  • Who is EDUCBA?
  • Sign Up
  •  
Free Courses
  • Free Online Excel Course
  • Free Vba Course
Certification Courses
  • All Courses
  • Excel VBA Course - All in One Bundle
  • VBA Course
  • Excel Data Analysis Course
  • Excel for Marketing Course
  • Excel for Finance Course
  • Excel for HR Training
Resources
  • Resources (A To Z)
  • Excel Charts
  • Excel Tips
  • Excel Tools in Excel
  • Excel Functions
  • Financial Functions in Excel
  • Logical Functions in Excel
  • Lookup Reference Functions in Excel
  • Maths Function in Excel
  • TEXT and String Functions in Excel
  • Date and Time Function in Excel
  • Statistical Functions in Excel
  • Information Functions in Excel
Apps
  • iPhone & iPad
  • Android
Support
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions

© 2019 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

Download INDIRECT Function Excel Template

By continuing above step, you agree to our Terms of Use and Privacy Policy.
EDUCBA
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
EDUCBA
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
EDUCBA
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
EDUCBA
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
EDUCBA

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
EDUCBA Login

Forgot Password?

Let’s Get Started
Please provide your Email ID
Email ID is incorrect