The Excel INDIRECT function

What does it do?

In brief summary, the Excel INDIRECT function returns a reference specified by a text string.

Syntax explained
=INDIRECT(ref_text,[a1])

Above you’ll see the syntax in Excel language, the part in brackets is where the magic happens so let’s break these both down and explain what each element does, but before we do, let’s explain what the term A1 and R1C1 style mean. This will affect the way you format this formula and also applies to other areas in Excel so it’s important to understand it’s relevance. A lot of sites won’t explain this without searching for it, so personally I’ve found it very helpful.

From the Excel Options menu under Formulas, you can at any time change the way the columns are labelled from A, B, C, D … etc (or A1 Style), to numbered columns, so A becomes 1, B becomes 2 and so on (or R1C1 Style). The R1C1 style is formatted to R for Row first and then C for Column. Compare this format to the A1 style, which, you guessed it, is Column first, then Row (reversed!) and you have a recipe for confusion!

In summary, A1 Style is the address co-ordinates of a cell, see below example where the SUM of C3 + D3 will result in an answer of 220.

summery

Click to enlarge

Using the same data set as above but changing the Formulas under Options to R1C1 style it looks a little something like this.

The RC[-3]+RC[-2] translates to Row(this row) and Columns(3 to the left) + Row(this row) and Columns(2 to the left). It yields the same result as above, but the address mechanism is relative to where the formula is. This is useful in VBA and more complex formulas.

translates to Row

Click to enlarge

Imagine yourself in the middle of the blue arrows and you want to move 1 cell in direction of one of the arrows, this should help you understand in more detail. It’s all relative to where you start.

blue arrows

So, with that out the way, let’s get back over to the syntax, see below:

Ref_text

As per Excel describes it as the “reference to a cell that contains an A1 or R1C1 style reference, a name defined as a reference, or a reference to a cell as a text string.”
Each option of the above is explored in more detail below under heading Examples.

[a1]

As per Excel, it defines this as “a logical value that specifies the type of reference in Ref_text: R1C1 style = FALSE; A1 style = TRUE or omitted.”

Examples
Ok, let’s make this a little clearer with some examples and pictures, see below:
INDIRECT in A1 Style

Indirect Style

Click to enlarge

In the example above, we have:

  • hand-o-right
    Some data, with a header, in RANGE (B2:B7)
  • hand-o-right
    A cell reference in the A1 Style shown in cell D3, you know what A1 Style this means if you’ve read the section under Sytnax explained above, if not, what are you doing here! Get back up there and read!
  • hand-o-right
    Finally, here’s our first simple INDIRECT formula, with the ref_text element as (D3). We’ve omitted the [a1] element, so the formula assumes this part to be TRUE (or A1 Style). Using INDIRECT(D3) yields the same results as INDIRECT(D3,TRUE)
  • hand-o-right
    In summary, the INDIRECT function has been asked to look at cell D3, and it should expect to find text in the A1 style of referencing. It then takes the discovery and uses it to return the information found in cell B3, in this case, it’s the word ‘This’.
indirect formula

Click to enlarge

Changing the reference in cell D3 from B3 to B7 will yield a different result, all making perfect sense, isn’t it?

INDIRECT in R1C1 Style

Change the Formulas in the Options from A1 to R1C1 style and the cell address of D3 is now changed as per the formula bar below. We now need to change the omitted (A1 style) part of the [a1] element to FALSE (R1C1 style), you’ll see from adding this in, it returns a #REF! result, that’s because the text in cell is NOT in R1C1 style.

Based on what you’ve seen so far, can you figure out what the R1C1 address would look like for cell B7? Remember? Clues are in the second paragraph under Syntax, what would the address be in numerical format?

numerical format

Click to enlarge

Got it? I knew you would, its R7C2. See below for the correction. It’s Row 7 and Column 2.

numerical format

Click to enlarge

Defined Name

If you don’t know about defined names, you’re about to. There’s number of ways to ‘name’ a range, we’ll look at one for now. If you have a range of data you want to reference, or a cell, in this example it’s the latter, simply highlight the cell and click in the address box shown with a blue highlight below.

highlight cell

Click to enlarge

Overtype this cell address with a name, in this case we’ll call it Ref

highlight cell

Click to enlarge

Now we can change the INDIRECT formula to look at the named range, in this case it’s looking for Ref, and we’ve just told Excel that we’d like cell D3 to be called just that.

indirect reference

Click to enlarge

Pressing enter will yield the same result again.

indirect-reference

Click to enlarge

Reference to a cell as a text string

In order to do this, we need to reference cell B7 as a text string, technically ‘B7’, is text but to elaborate on this and include the sheet name too, it’ll make a whole lot more sense when you realise it’s potential to make spreadsheet super dynamic. See last example below for more detail.

If you were to go to another sheet in the same workbook and in any cell, press = and the then click on your sheet with data (in this case the sheet is called Test Data), and click cell B7. Pressing enter would give you a formula like below.

='Test Data'!B7

This is the full text string address of where we find example on sheet Test Data. Copy the text from this cell (excluding the = sign) and paste it into the INDIRECT formula over the previous example Ref and it’ll look a little like below. Remember, this is text, so in order to tell the formula it’s text, we need to enclose it with quotation marks either side.

Test Data

Click to enlarge

I’ve put double spaces between the quotation marks and the copied text, in order for this to work you need to delete those spaces, like below, and giving us the expected result.

Test data1

Click to enlarge

The latter is one of my favourite uses when trying to summarise any data on one sheet from many and you want a degree of flexibility without having to change the formulas every time. See below.

Here I’ve used INDIRECT combined with CONCATENATE to give the text string address of a cell with the total for January, as found on the sheet titled Jan and returning the data found on that sheet in cell C9.

summery data

Click to enlarge

summery data

Click to enlarge

What I like about this is I can now just change the month name on the Summary sheet to Feb or Mar and it’ll update to look at that sheet straight away.

As with any formula, they have their limitations, right times to use them and wrong times. Mistakes will be made, things will be learnt, that’s how it goes with Excel, you’ll sit there for a while looking at an R1C1 address only to realise you were calculating Column first THEN Row, I’ve been there!

Risks with this latter example, note that the cell address is fixed to cell C9 only, move that row out of that address and the formula will not work!

Now before you go, grab hold of my 27 formulas book. While INDIRECT isn't one of them, I hone in on the 27 formulas that will really simplify your work. Sign up below and get a free copy in your inbox.

    Sohail Anwar