How to use the INDIRECT function in Excel

You may already be used to using cell references in Excel, but did you know you can take it a step further and create your own cell references? This is where the INDIRECT function comes in handy.

What does the INDIRECT function do?

Excel’s INDIRECT function turns a text string into a link. This feature has several practical advantages:

• It allows you to create dynamic links. For example, you can change a cell reference within a formula without changing the formula itself.

• The INDIRECT function is a great way to create links between sheets in a workbook.

• You can use information already in your spreadsheet to create the link.

• The link created using the INDIRECT function will remain the same even if the structure of your worksheet changes.

INDIRECT syntax

By default, links in Excel use the A1 link style, which means that the links refer to the column first and then the row number. However, the INDIRECT function allows you to switch them.

This inverted order is known as the R1C1 referencing style, where “R” refers to the row and “C” refers to the column. For this reason, the INDIRECT function contains two arguments. Here’s the syntax:

=INDIRECT(x,y)

Where X this is a reference text, and th (optional) – This is a reference style.

More specifically, X (link text) can be an A1-style link, an R1C1-style link, a predefined name, or a cell link. If X references another workbook, that second workbook must be open. It’s also worth noting that you can’t link to another workbook if you’re using Excel for the web.

th (link style) is an optional argument. If you choose not to include this argument, Excel will automatically use the A1 style link by default, although you can also enter true here to force Excel to use this link style. Entering text LIE will tell Excel to use the R1C1 reference style.

In this article, I will use A1 style links to show you how the INDIRECT function works. Once you understand the concept, you can experiment using R1C1 style links.

The result of the INDIRECT function is a link.

Things to keep in mind before using the INDIRECT function

Before you start using INDIRECT in your spreadsheet, there are a few things you should know:

1. This is an unstable feature, meaning it is constantly trying to update itself. Using this function on a large spreadsheet may cause it to run slowly or even not work.

2. To take full advantage of the INDIRECT function, it will be helpful for you to first learn how to create named links in Excel.

3. The ampersand (&) operator is a great way to create a text string that can be used as a reference.

How to use the INDIRECT function

To help you understand how to use INDIRECT in practice, let’s look at how it works in its most basic form.

Basic examples

For this example, enter

=INDIRECT(A1)

to cell B2 turns cell A1 into a reference to cell D1. That’s why the result is 5.

For this example, enter

=SUM(INDIRECT(A2))

to cell B2 first converts A2 to a cell reference and then sums the cells in that reference. This is because the INDIRECT function is built into the SUM function.

Now let’s look at using the INDIRECT function to reference a cell on another sheet. Cell A1 of Sheet2 contains the number 100, and we want to create an INDIRECT reference to this cell in the current sheet. First we need to enter Sheet2!A1 to cell A3 and then we need to turn this into a link using the INDIRECT function by typing

=INDIRECT(A3)

to cell B3.

However, we can make this much easier by using a named reference. For example, if we renamed cell A1 in Sheet2 GENERALwe could use this in our INDIRECT reference. This is especially useful if you’re linking to another sheet because it helps avoid having to remember the exclamation point in the syntax. Also, if your sheet name changes, the INDIRECT reference will not adapt to that change, so using a named reference is a safer and more permanent alternative.

Real example 1

Now that we understand how INDIRECT works, let’s look at how we can use it to make our worksheets work in the real world.

As is often the case in Excel, there are multiple ways to achieve the same result. For example, you can enable the TAKE function to make the next process even more dynamic. However, our goal here is to demonstrate the use of the INDIRECT function so that you can then use it in your own way.

Here we are tracking the number of goals scored by a football team in each game and want to calculate the average for the previous three games.

First, we’ll use the COUNT function to determine how many games a team has played so far. Entering text

=COUNT(B:B)

cell E1 will count the total number of cells in column B containing numbers, thereby telling us how many games have been played.

If you want to count text as well as numbers, use the COUNTA function instead.

We are now ready to use the INDIRECT function to turn the data in cell E1 into a link. For now, our goal is to find the average of cells B6 to B8. Here’s how the INDIRECT function will help us do it. Start by typing

=AVERAGE(INDIRECT

in cell E2 because we’re telling Excel that we ultimately want to find the average, and we’re also using the INDIRECT function to tell Excel where to look for that average.

The first cell we will refer to in our AVERAGE calculation is B8, so we will add

=AVERAGE(INDIRECT("B"&E1)

according to our formula. Notice how we used quotation marks around the column reference, since we are using this text to create a cell reference.

The next cell we will refer to in our AVERAGE calculation is B7, which is one cell above B8. So by adding

=AVERAGE(INDIRECT("B"&E1),INDIRECT("B"&E1-1)

to our formula means that Excel will create a cell reference that starts in column B and then minus 1 from the value in E1.

Finally, we want to reference cell B6, which is two cells above B8 in our AVERAGE calculation, and then add a closing parenthesis:

=AVERAGE(INDIRECT("B"&E1),INDIRECT("B"&E1-1),INDIRECT("B"&E1-2))

This correctly calculates the average of 3, 1 and 1, which is the total number of goals scored in the last three games.

Now, as we add more data to Column B, the value in cell E1 will increase because the COUNT function will recognize that there are more values ​​in Column B and the INDIRECT links will update accordingly to always capture the last three values ​​in our AVERAGE. formula. All this happens without having to change the formula we entered in cell E2.

Real example 2

Let’s look at another example. This time we will use named references so that we can use INDIRECT with the VLOOKUP function in Excel.

We want Excel to tell us how much money each of the four people made in the first or second week, with the ability to change that week number if necessary.

To start, we need to name cells B1 to C5 as “week_1” and cells B7 to B11 as “week_2”. Then in cell G2 we will enter

=VLOOKUP(E2,INDIRECT("week_"&F2),2,0)

because we want Excel to look for the Volume value in a range we called “week_1” and return the corresponding value in the second column of that range as an exact match.

We can then use the padding handle to complete the data for the other three individuals, knowing that the links we create refer to both the corresponding rows and are also safe due to our use of named links.

Then any changes to the values ​​in columns C or F will be automatically applied to the formula you just wrote.

---

To complete the process of making your spreadsheet completely dynamic and neat, you can add drop-down menus using Excel’s data validation tool. In the example above, you could make people’s names available via a drop-down menu so that the lookup table only takes up one row.