• Skip to main content
  • Skip to primary sidebar
  • Skip to footer

Microsoft Excel Tips and Tricks - Computergaga

  • Home
  • Excel Online Courses
  • Excel Functions List
  • Free Downloads
  • Blog
  • Tutorials
  • About
Home / Formulas / Excel INDIRECT Function – 5 Examples

Excel INDIRECT Function – 5 Examples

In this blog post, we look at 5 examples of the INDIRECT function in Excel. This is a very misunderstood function. It is an incredibly useful function.

Prefer to watch the video? The video tutorial below will demonstrate all 5 Excel INDIRECT function examples.

Download the sample INDIRECT function workbook to practise the examples.

What is the Excel INDIRECT Function?

Before I delve into these different INDIRECT examples, let’s learn the anatomy of this wonderful function and how it operates.

The INDIRECT function is one of the lookup functions of Excel. And it’s purpose is to return a reference which is specified by text (typically some text in the cell of a worksheet).

This becomes really useful to create references to other sheets, named ranges and tables from a cell value. You will see these in the following examples.

The Excel INDIRECT function looks like below.

=INDIRECT(ref_text, [A1])
  • Ref_text: A reference to a cell that contains, or partially contains, an address.
  • A1: This argument is optional. It determines the type of reference for INDIRECT to use. If you type TRUE then the normal A1 style is used. If you type FALSE, the R1C1 style is used.

Ok, let’s get stuck into these tutorials.

1. Using INDIRECT with Named Ranges

A great use for INDIRECT is with names. There are two main types of names in Excel – tables and named ranges.

The concept of using names for simpler referencing has huge benefits, and when you understand INDIRECT those benefits are amplified. This example demonstrates the Excel INDIRECT function with named ranges.

In the example below, cell G4 contains the total for the sales of the region mentioned in F4. In this image, it is for Cardiff.

Summing the values from a column specified by a cell value

This total was achieved using the formula below.

=SUM(INDIRECT(F4))

Each range of values has been named after the region. For example range D3:D10 is named Cardiff, and range B3:B10 is named Southampton.

The Excel INDIRECT function returns the value from F4. This value is then converted from text to a reference so that the SUM function can add the values from the named range.

Without INDIRECT the SUM function would not use the value in F4 as a reference to the named range.

Watch this video for an example of the INDIRECT function in Excel with table references.

2. INDIRECT to Dynamically Reference other Worksheets

In this example, we want to reference a different worksheet within a formula, which is another SUM function. The worksheet we are summing values from is dependent upon the value in a cell. So once again, INDIRECT is needed.

The image below shows the summary sheet, and cell B3 contains the name of the sheet that we will be summing from. The beauty of this technique is that if someone changes the value in cell B3, it then sums from that sheet.

Dynamically reference a worksheet with INDIRECT in Excel to be summed

The formula below has been used in this example. In each sheet the values to be summed are in range C4:C11.

=SUM(INDIRECT("'"&B3&"'!C4:C11"))

The apostrophes have been used in the formula to enclose the sheet name. If a sheet name contains spaces, such as South Africa, apostrophes are required.

3. Return the Last Value from a Row

In this example, we will look at using an R1C1 style reference with INDIRECT to return the last value from a row.

In the image below, the formula in cell F3 is returning the last value in row 10. This ensures that as new months (columns) are added to the range, it always retrieves the current months value.

Return the last value in a row with an INDIRECT formula

The formula below has been used in cell F3. By using an R1C1 reference style instead of the traditional A1 style, we can refer to the column numerically. The COUNTA function has been used here to count the non blank cells of row 10, therefore returning the last column.

=INDIRECT("R10C"&COUNTA(10:10),FALSE)

This is concatenated onto the R10C part to complete the reference as row 10 and column 4.

The FALSE part of the INDIRECT formula requests the R1C1 style reference.

4. Excel INDIRECT Function with VLOOKUP

It feels necessary to include an example of using INDIRECT with the VLOOKUP function. For this example, INDIRECT will be used to create a conditional lookup table.

Of course, techniques such as this will work with lookup formulas such as XLOOKUP and FILTER also.

In the image below, a VLOOKUP function has been entered into cell B3 to return the sales value for Jessica (B1) and Cardiff (B2).

Excel INDIRECT function used to create a conditional lookup table for VLOOKUP

Each lookup table has been named appropriately as Southampton, Liverpool and Cardiff. Just like in the first example of this blog post, INDIRECT has been used to convert the value of cell B2, to a reference to a named range.

The formula below, uses VLOOKUP to return a value from a table, specified by the value in cell B2. The INDIRECT function is entered into the table array argument of VLOOKUP.

=VLOOKUP(B1,INDIRECT(B2),2,FALSE)

5. Create Dependent Drop Down Lists

Another brilliant use of INDIRECT is to create dependent drop down lists. This is a great technique if you work with large lists.

You can break the large list up into multiple smaller lists and then make them dependent on one another. So the selection from one list determines the options that appear in the next list.

In the image below, each range of cities has a name defined that matches the country name. It is essential for this technique, that the country name in range A2:A4 matches the named range for each country. This is because INDIRECT will reference the named range from the list item.

Named ranges assigned for each country list

The following Excel INDIRECT formula can then be used for the list source in the Data Validation window. Cell F2 contains the value from the first drop down list.

=INDIRECT(F2)
Excel INDIRECT function in Data Validation window

Now, when an option is chosen from the first list, the second list shows only the options that relate to that selection.

Dependent drop down list from the Excel INDIRECT formula

Check out this video on how to create multiple dependent drop down lists.

If any of these INDIRECT examples are not clear, check out the video at the top of this blog post which will show you exactly how each formula is done.

Related Posts:

  • Excel FILTER Function – The Best Function in Excel
    Excel FILTER Function – The Best Function in Excel
  • Display Negative Time in Excel
    Display Negative Time in Excel
  • Sort by Drop Down List in Excel
    Sort by Drop Down List in Excel
  • Create a Picture Lookup in Excel
    Create a Picture Lookup in Excel

Reader Interactions

Leave a Reply Cancel reply

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

Primary Sidebar

Popular Posts

  • Excel Fixtures and League Table Generator
  • Excel IF Function Contains Text – A Partial Match in a Cell
  • 5 Reasons Why your Excel Formula is Not Calculating
  • Excel Formula to Display the Sheet Name in a Cell
  • 4 Alternatives to Nested IF Formulas
  • How to Create a Reverse FIND Formula
  • Check if a Value is an Even Number
  • How to Hyperlink to a Hidden Worksheet in Excel
  • IF Function in Power Query Including Nested IFS
  • Conditional Formatting Multiple Columns – 3 Examples

Recent Posts

  • SEQUENCE Function in Excel
  • Sort by Drop Down List in Excel
  • Advanced SUM Function Examples – The Power of SUM
  • Display Negative Time in Excel
  • Lookup Multiple Values in Excel
Advanced Excel Success book

Footer

Contact

[email protected]

Useful Links

  • Home
  • Excel Online Courses
  • Tutorials
  • Sitemap
  • Privacy Policy
  • Jobs by Jooble

Course Topics

  • Charts
  • Courses
  • Dashboards
  • Downloads
  • Excel Tips
  • Formulas
  • Mail Merge
  • Office
  • PivotTables
  • Power BI
  • Power Pivot
  • Power Query
  • VBA

Join Our Mailing List

© Copyright 2020 Computergaga · All Rights Reserved ·