Excel INDIRECT Function: Formulas and Examples (2023)

Some functions of Excel are simply meant to amaze you. The INDIRECT function is also one of that lot ๐Ÿ™ˆ

Technically speaking, it will not perform any operation, nor will it run any logical tests to return Boolean Values. It is a unique function thatโ€™s different from all other functions of Excel.

If you have had too much suspense already, let’s just dive right into the guide below. What is the INDIRECT function, how is it used and what is it used for โ€“ we will answer that all in there. ๐Ÿ‘‡

As you continue reading, do not forget to download our free sample workbook thatโ€™ll help you tag along with the guide.

How to use the INDIRECT function

The INDIRECT crates an indirect reference to cells, ranges, sheets, and workbooks, too ๐Ÿ”„

Hard to get until we see an example of how it is applied, so here we go.

The image here has some value in Cell A2.

Cell Address A2
  1. Write A2 in another cell (Cell B2).

Make sure you don’t write it as =A2, or else Excel will create a link to Cell A2 in Cell B2. Right now, we are only writing A2 as a simple text โœ

Kasper Langmann, Microsoft Office Specialist
  1. In another cell, write the INDIRECT function as follows:

= INDIRECT (B2)

Writing the INDIRECT function

We have created a reference to Cell B2 (that contains the text string A2).

  1. Hit Enter.
The INDIRECT formula refers to Cell A1

Magic ๐Ÿ’ฅ Whatโ€™s that?

The Excel INDIRECT function returns the value of Cell A2.

From the backstage view, the INDIRECT function turned the text string in Cell B2 (A2) into a cell range and created an indirect reference to it.

So what if our data was not just a single cell but a column long like here ๐Ÿ‘€

A column-long logical value data

Not a problem!

  1. In Cell B2, write the cell range A2:A5.
The cell range A2:A5
  1. Again write the INDIRECT function just like above.

= INDIRECT (B2)

Excel INDIRECT function syntax
  1. Hit Enter.
The INDIRECT function returns the result

This time the INDIRECT function treats the text string A2:A5 as a range and returns the whole of it ๐Ÿ˜

Pro Tip!

Whenever you delete, add, or move cells in Excel, it automatically updates the cell references in formulas.

The INDIRECT function doesnโ€™t do that. For example, if you add a row before Column A in the example above, the column containing the data will move ahead and become Column B.

The INDIRECT function will not update the results and return the same cell range A2:A5 ๐Ÿ˜Ž

The INDIRECT function doesnโ€™t update results

But how does it make sense to use it practically? We will see that in the examples below ๐Ÿ’ก

Other INDIRECT formula examples

We are on our way to more interesting examples of the INDIRECT function โ€“ you are sitting in tight, right?

Reference to other sheets (with and without CONCATENATE)

Using the INDIRECT function, you can not only create indirect references to other cells from the same sheet but to other sheets too.

For example, here is Sheet 2 of an Excel workbook. Where Cell A1 contains a value ๐Ÿš€

worksheet no.2 of a workbook

Pro Tip:

Note that when we create a reference to cells from other sheets, the reference looks like the below:

Sheet Name + Exclamation Mark (!) + Cell Reference

So, if we create a reference to Cell A1 from Sheet 2, it will look like this:

Sheet2!A1

Reference to another sheet

We are now in Sheet 1 of the same workbook. To create an indirect reference to Cell A1 from Sheet2:

  1. Activate a cell (Cell A2) and write the sheet reference below:

Sheet2!A1

Writing the sheet reference
  1. In another cell, write the INDIRECT function as follows:

= INDIRECT (A2)

Writing the INDIRECT function
  1. Hit Enter and you have your indirect reference to another sheet all set โœŒ
Reference to another sheet

We have the value from cell A1 of Sheet 2 in Sheet 1. Yay!

If you find it hard to write the cell reference from another sheet like that, you may use the CONCATENATE function as below:

  1. Write the name of the sheet and cell reference in different cells. And merge them with an exclamation mark in between to get the sheet reference like below.

= CONCATENATE (A2,โ€!โ€, B2)

The Sheet reference

Must not forget to enclose the exclamation mark in double quotation marks ๐Ÿ—ฏ

Kasper Langmann, Microsoft Office Specialist
  1. Wrap the above function in the INDIRECT function.

= INDIRECT (CONCATENATE (A2,โ€!โ€, B2))

INDIRECT and CONCATENATE
  1. Hit Enter to reach the same results again.
Reference to Sheet 2

Fun, isnโ€™t it ๐Ÿญ

Performing functions on Named Ranges

The INDIRECT function can not only recognize cell references and ranges but named ranges too. See for yourself.

Here is a list of some expenses (yeah, many of them) ๐Ÿ’ฐ

List of expenses

Turn them into a named range by following these steps:

  1. Select the list of expenses (the numbers only).
Selection of column
  1. Go to the Formulas tab > Name Manager.
The Name manager
  1. From the Name Manager dialog box, select New.
Setting a new named range
  1. Set a name for the defined range (we are setting it to Expenses).
Setting the name to Expenses
  1. Click Okay.

The range is saved by the name Expenses ๐ŸŽฏ

Range saved
  1. Close the Name Manager.
  2. Activate any cell (Cell D2) and write the name of the range (Expenses) in it. Make sure not to make any spelling mistakes.
Writing the name of the range
  1. In another cell write the INDIRECT function as follows:

= INDIRECT (D2)

Writing the name of the range
  1. Hit Enter to see that the INDIRECT function will return the whole range that you saved by the name Expenses ๐Ÿ’โ€โ™€๏ธ
The whole range returned

Cool! But we will take it a step ahead.

  1. Now wrap the INDIRECT function in the SUM function as below:

= SUM (INDIRECT (D2))

Wrapping the INDIRECT function in the SUM function
  1. Hit Enter.
The SUM of the range

Viola! We have the sum of the range Expenses. Thatโ€™s how you can use the INDIRECT function to create an indirect reference to ranges ๐Ÿ’ช

You may try any function with that. For example, wrap the INDIRECT function above in the AVERAGE function to get the average of the said range.

= AVERAGE (INDIRECT (D2))

Average of the range

Pro Tip!

Named ranges are not like cell references.

Even if you add, move or delete cells before or after a named range (Expenses), the answer to the INDIRECT function won’t change. See that below ๐Ÿšดโ€โ™‚๏ธ

  1. Add a column behind Column A as below
Adding a column behind Column A for data validation
  1. The answer to the INDIRECT function remains unchanged. That’s because the range of Expenses remains unchanged ๐Ÿ†
Answer remains unchanged

That’s it – Now what?

Through the above guide, we have learned the primary use and operation of the INDIRECT function in Excel.

Not to forget, we have also seen multiple examples of you can bring it to use practically. Hope you enjoyed learning about it ๐Ÿง

Because if you did, your learning journey has yet only begun. There are so many more functions in the Excel library that you must explore.

To begin with, I suggest you learn about the VLOOKUP, SUMIF, and IF functions (my top favorite Excel functions).

My 30-minute free email course is accessible here. It is designed to teach you these and (many more Excel functions) in the shortest time.

Other resources

The INDIRECT function can only create an indirect reference to another cell. But how do you create a direct reference in Excel?

There are three kinds of direct references that you can create in Excel – learn about them all here.

Frequently asked questions

The INDIRECT function in Excel creates an indirect reference to a cell, sheet, or workbook.

For example, if Cell A1 has the value 20. And Cell B1 has the value A1.

Write the INDIRECT function as follows:

= INDIRECT (B1)

And the result would be 20. Cell B1 contains the address for Cell A1, and A1 contains 20.

The cell that’s referred to as the ref_text can contain a range. For example, you can write the INDIRECT function as INDIRECT (B2) when Cell B2 contains the value A2:A10 (a cell range).

However, if you write the INDIRECT function as INDIRECT (B2:B7), or if ref_text is not a valid cell reference, the function would return the #VALUE error.