How to Use the Excel INDIRECT Function (2024)

Why Use the INDIRECT Function

The INDIRECT function in Excel is a handy tool for anyone who uses data from different parts of a workbook. It lets you link to data in other sheets without directly typing cell locations.

This is useful if you often need to update data sources or if your data is spread across many tabs.

For example, if you’re tracking yearly sales data for different products, each stored on a separate sheet, INDIRECT can help you create a summary sheet.

You can set it up to automatically pull information based on the product or year you choose. This saves time and ensures your analysis is always up-to-date, helping you make informed decisions without constant manual adjustments.

How to Use the INDIRECT Function

The INDIRECT function in Excel is a tool that converts text into a cell reference. This function is particularly useful for retrieving data from a cell specified in another cell.

Syntax:

=INDIRECT(ref_text, [a1])

• First input (ref_text): Here, you input the cell reference. You can type it directly as text, like “A1”, or you can use another cell that contains this reference as text.
• Second input (optional): This input decides the style of cell reference. By default, or if set to True, Excel uses the A1 style (column letter followed by row number). If set to False, it uses the R1C1 style, which uses both row and column numbers.

This means:

• If you type =INDIRECT(“A1”), Excel shows you the contents of cell A1.
• If you enter =INDIRECT(“A1”, FALSE), Excel expects the reference to be in the R1C1 style. Since “A1” is not in that format, this will result in an error.

This structure ensures you always know where your data points without manually updating cell references, making your spreadsheets more efficient and less prone to error.

Excel Column Number: R1C1 Reference Style

In Excel, the R1C1 reference style is a way of identifying cell locations using numbers for both rows and columns. For example, what is called cell A1 in the usual system is referred to as R1C1 in this style, meaning Row 1, Column 1.

By default, if you don’t specify a format, the INDIRECT function in Excel uses the common A1 style.

Example:

Suppose you have the text “I6” in Cell A6, and “Hello there…” in Cell I6.

If you go to Cell B6 and enter the formula =INDIRECT(A6), it will display “Hello there…”.

Essentially, the function looks at what’s in A6, sees “I6”, and fetches the content from Cell I6.

The INDIRECT function also has an optional second argument to specify the referencing style:

• If you use FALSE, or leave this argument out, Excel uses the A1 style (the usual way of referencing cells, like A1, B2, etc.).
• If you use TRUE, Excel switches to the R1C1 style, where cells are referenced using both their row and column numbers (like R1C1 for A1, R1C2 for B1, etc.).

❗If the text in A6 doesn’t direct to an existing cell (like if it’s empty or nonsensical), you’ll get an #N/A error because Excel can’t find a valid reference.

Changing a Cell’s Address in Excel

You can refer to a cell by a custom name instead of the standard A1 format. Here are two simple ways to do this:

Option 1:

1. Click on the cell you want to rename.
2. In the upper left corner, just next to the formula bar, you’ll find the Name Box. Enter the new name for your cell there.

Option 2:

1. Go to the ‘Formula’ tab on the ribbon.
2. Click on ‘Name Manager’, then on ‘New’.
3. Type the new name for your cell and specify which cell it refers to.

For example, after naming cell I6 as “greeting”, you can refer to it as “greeting”. To see how this works with the INDIRECT function, enter this formula in cell B7:

= INDIRECT(A7)

If A7 contains the text “greeting”, INDIRECT will display the content of cell I6, say “Hello there…”.

In the same way, rename cell A6 to “hello” and write the function in cell B6 as follows:

= INDIRECT(A6)

Since cell A6 has been renamed to “hello,” using the INDIRECT function to reference A6 will display the contents of cell A1, which are currently set to “INDIRECT Function.”

❗Important Tip:
When using INDIRECT, avoid enclosing the reference in quotation marks unless you want the text itself.

For instance:

= INDIRECT("A6") 

The INDIRECT function will treat “A6” as text, not a reference, showing what’s in A6.

The beauty of INDIRECT is its dynamic nature; if the content of the referenced cell changes, the output of your INDIRECT function will update automatically. For example, changing “Hello there…” to “Hello there!” in cell I6 will reflect in cell B7 immediately.

Using the INDIRECT Function to Reference Data in Different Sheets in Excel

If you often need to pull data from tables located on different tabs, using the INDIRECT function can simplify your workflow, eliminating the need for complex nested IF statements.

Example Scenario:

In this example, two data tables named ‘data_py’ and ‘data_current’ are located on separate tabs, Data_2016 and Data_2017, respectively. Each table contains a revenue column for various divisions across different regions.

Steps to Use INDIRECT with SUMIFS for Dynamic Data Lookup:

Identify the Table Name:

• Click on the table.
• Under Table Tools > Design, find the table name displayed at the top left.

Create a Dynamic SUMIFS Formula:

• Go back to your main tab.
• Start by writing a static SUMIFS formula to sum revenues based on divisions:

= SUMIFS(data_py[Revenue], data_py[Division], Report1!B15)

• Drag this formula down to apply it across multiple rows. This will give you the sum of revenues for each division in year 2016.

Make the Formula Dynamic:

The aim is to make the SUMIFS function dynamic so that when you select the year 2016, it goes to the tab containing 2016 data, but selecting the 2017 sends it to the other tab. Go to the helper cells and write down the years that are specified in the dropdown option, 2016 and 2017. Specify the name of the data table corresponding to each year.

This is where we use a combination of the INDIRECT and VLOOKUP functions. But in order for this method to work, the headers of both tables need to be identical. To minimize the confusion of using multiple nested formulas, you can use helper cells which serve as intermediate values. This way, you can have the VLOOKUP values here and reference to these cells instead.

Use the VLOOKUP function to give you the name of the table containing the data for year selected in cell C14 and combine it with the header names. The ampersand (‘&’ symbol) concatenates both terms to give you the final reference.

Refer to the Revenue column by using the formula in cell K15:

= VLOOKUP(C14, H15:J16, 2, FALSE) &"[Revenue]"

This results to data_py[Revenue]. Do the same for the Division in cell L15:

= VLOOKUP(C14, H15:J16, 2, FALSE) &"[Division]"

This gives you data_py[Division]. You will notice that if you change the year to 2017, the values in cells K15 and L15 automatically change to data_current[Revenue] and data_current[Division]. These can now be used as the reference text for your INDIRECT function.

Rewrite the SUMIFS formula in cell C15:

= SUMIFS(INDIRECT($K$15), INDIRECT($L$15), Report!B15)

Since you want to be able to drag the formula in C15 down to C17 while retaining to the correct formulas, make sure the reference cells are fixed as $K$15 and $L$15.

Another option is to eliminate the use of the helper cells and do the VLOOKUP directly inside the INDIRECT formula which makes the final formula in cell C15:

= SUMIFS(INDIRECT(VLOOKUP($C$14, $H$15:$J$16, 2,FALSE) &"[Revenue]")), INDIRECT(VLOOKUP($C$14, $H$15:$J$16, 2, FALSE) &"[Division]")), Report!B15)

Download the Workbook

Feel free to Download the WorkbookHERE.