Excel INDIRECT function: 7 Formulas to Understand How it Works

Generally, we write formulas in Excel with hard-coded cell references, range references, names etc. For example:

=SUM(A2:A10)

In future, if you want to add more cells in SUM function then you need to modify the formula. The INDIRECT function is used when you require changing the reference to a cell in a formula while you do not want to change the formula itself.

Sounds tough and boring? Hopefully, the examples coming up in the sections below will make it clearer and you will see its usefulness.

Syntax for using INDIRECT function

The general syntax of using the INDIRECT function is:

INDIRECT(ref_text, [a1])

Where:

Ref_text is required This is where you will specify the text string and INDIRECT function returns the reference based on this. The returned reference is immediately evaluated to display its content.
The a1 argument is optional. It has two possible value: TRUE/FALSE. The default value is TRUE which means interpret ref_text as the A1-Style reference. The FALSE value means R1C1-style reference.

A simple example to begin with INDIRECT function

The first example to understand how INDIRECT function works is pretty simple. In the Excel sheet, I assigned text to the A columns cells and in the B column, I used the INDIRECT function as follows.

In the Excel sheet you can see these formulas with results:

=INDIRECT(A2)

It displayed the value in B2 cell because A2 cell contains the reference to B2.

=INDIRECT(A3)

Resulted in #REF! as A3 cell contains invalid cell reference.

=INDIRECT(A5)

Returned the B5 cell’s text.

Using the name reference

The following example shows using the name in the INDIRECT function. For that, I have given a name to the B5 cell and see the result.

The formula:

=INDIRECT(A5)

As A5 contains “indir_test” that refers to the B5 cell, so it displayed B5 value.

The example of using SUM and INDIRECT functions

In this example, we have two numeric columns. One containing the “Total Number of Sales” and the other contains Amount. Both of these column’s cells are given names; Sales and Amount, respectively.

Next, I used SUM and AVERAGE formula to get the total of Sales and Amount range names. These are used in the INDIRECT functions as follows:

I have created two dropdowns that contain the names of ranges (Sales and Amount). The SUM/INDIRECT function referred the B16 cell that contains dropdown:

=SUM(INDIRECT(B16))

As you select Sale/Amount from the dropdown, its sum is displayed in the adjacent C16 cell.

Similarly, the AVERAGE/INDIRECT formula is used as follows:

=AVERAGE(INDIRECT(B17))

Make sure that dropdown values contain the same name as the named ranges are, otherwise, #REF! error occurs.

Locking the cells example

One of the benefits of using INDIRECT function is it can lock the cells. The advantage of this in cases where you require inserting row(s) or columns after using the INDIRECT formula. For example, in our Number of Sales and Amount columns, if we apply these formulas to get the sum:

=SUM(B2:B13)

To the Number of Sales and this is used to get the Amount sum:

=SUM(INDIRECT(“C2”):C13)

The result is:

If we require inserting a row after headers row on top with values then the SUM formula will adjust itself from:

=SUM(B2:B13)

=SUM(B3:B14)

Whereas, the formula with SUM/INDIRECT will be:

1

=SUM(INDIRECT("C2"):C14)

So, if you insert the values for Number of sales and Amount, the resultant sheet will be:

You can see, the Number of Sales new value (100) is not added to the total whereas the Amount is showing addition of 10000.

Using INDIRECT function with different sheet example

You may also refer some other sheet within the same workbook as using the INDIRECT function. See the simple formula where I used the sheet name in the INDIRECT function.

The INDIRECT formula for referring another sheet:

=INDIRECT(“INDIR!” & B2)

In the formula, we referred INDIR sheet’s B2 cell.

You may also reference another sheet by referring a cell:

1

=INDIRECT(A2 & "!" & B2)

The result:

In the left sheet (INDIR), you can see B4 cell contains 114. In the INDIRECT formula, I referred A2 cell (in ne the sheet) that contains INDIR value. This points to the other sheet we want to refer in the INDIRECT function.

The other part refers the B2 cell in new sheet that contains B4 value. Thus, the INDIRECT returned the B4 value in “INDIR” sheet.

How to refer some other workbook sheet?

In above example, we referred another sheet in the same workbook. You may also refer a sheet from some other workbook. For that, you have to use the other work book name, its sheet and cell reference that you want to get the value.

A sample formula is shown below: