There are many #Excel functions that you may not find them useful when you first learn them. Sometimes you may even doubt why there are such functions. INDIRECT should be one of them. 🤔

What INDIRECT does?

It returns the reference specified by a text string.

The syntax is simple:

=INDIRECT(ref_text, [a1])
where ref_text is required.  It is the text string describing the cell reference
[a1] is optional.  When it is omitted or TRUE, Excel treat ref_text as A1-style reference; when it is FALSE, Excel treats ref_text as R1C1-style reference.

Common examples are in the simplest form.

Say in A1, we input something like B2, B4, D10, etc. (any valid cell reference). And then in another cell, say B12, we reference to A1 using

=INDIRECT(A1)

When we change the value in A1, the formula in B12 returns another value based on the text input in A1. The following screencast shows the effect:

Now I hear you:

Why don’t we simply use a direct reference? We can do the same by inputting the simple direct cell reference in B12

=B2 

and we can change the formula to =B4, B6, D10, or whatever valid cell reference, easily whenever needed. What’s the point of using INDIRECT and reference it somewhere else?

I had the same question too… until I found a valid use case of it.

You may download a sample file to follow a long.

There is a common use case in daily work that I find INDIRECT can be useful.

Situation

Imagine we want to extract certain information from various cells in a worksheet, highlighted below, and put them onto a “Summary” sheet.

We can do it by referencing the cells directly.

On the “Summary” sheet, we input the following in range of C3:J3 respectively.

=Ada!C2 
=Ada!C3
=Ada!C4
=Ada!C15
and so on and so forth...

That’s easy…

…only when we need to deal with one or two worksheets:

How about when we have 5 worksheets?

Doing the math, we know that we need 40 different formulas.

How about when we have 20 worksheets, or even more? 😣

Do you think it’s still ok to do the “direct” reference one by one?

Here comes INDIRECT to rescue!

With little setup, we can get all the data with one formula:

=INDIRECT($A3 & C$1)'play attention to $ placements
where A3:A7 resides the worksheet name; 
C1:J1 resides the cell references

How does it work?

We use the & operator to construct the cell references where we need the data.

In C3, ($A3 & C$1) returns a text string of "Ada!C2"

The resulting text string is then fed into INDIRECT

=INDIRECT(“Ada!C2”) returns the value resides in the cell reference stated indirectly. In plain English, give me the value in C2 on the sheet “Ada”, please Excel.

When the formula

=INDIRECT($A3 & C$1)

is copied down and right, the mixed reference moves accordingly, telling Excel the exact locations for the data required.

Instead of 40 formulas, one formula does it all!

Sometimes we need to be INDIRECT in order to get the result we want in an efficient way. 😄

Isn’t it nice?

Note: INDIRECT is a volatile function. It may slow down your workbook when used extensively. Having said that, it should be fine for normal scenarios.

Watch it in action