INDIRECT Function

The INDIRECT function returns a valid cell reference from a given text string. INDIRECT is useful when you need to build a text value by concatenating separate text strings that can then be interpreted as a valid cell reference.

INDIRECT takes two arguments, ref_text and a1. Ref_text is the text string to evaluate as a reference. A1 indicates the reference style for the incoming text value. When a1 is TRUE (the default value), INDIRECT evaluates ref_text as an "A1" style reference. When a1 is FALSE, INDIRECT evaluates ref_text as an "R1C1" style reference. For example:

=INDIRECT("A1") // returns a reference to A1
=INDIRECT("R1C1",FALSE) // returns a reference to A1
=INDIRECT("C5") // returns a reference to C5
=INDIRECT("R5C3",FALSE) // returns a reference to C5

Note: the a1 argument only changes the way INDIRECT evaluates ref_text, not the result.

Understanding the INDIRECT function

Out of all Excel's many functions, INDIRECT might be the most confusing to new users. The purpose of INDIRECT at first seems baffling — why would you use text for a reference when you can simply provide a normal reference? One reason is that you already have a reference as text (perhaps in a cell) and you need a way to make Excel understand it as a reference. Another reason is that you want to assemble a reference in a formula using different bits of information. With text, you can easily hardcode some values, pick up other values from the worksheet, and join the values together using concatenation. The challenge however is that once you have created a reference as text, Excel won't recognize it as a reference. To Excel, it's just a text value. You can think of the INDIRECT function as a magic wand that converts the text to an actual reference.

Things to know about INDIRECT

Here are some things you should know about the INDIRECT function:

• The input to INDIRECT is text. You can create this text any way you like.
• INDIRECT will evaluate the text and convert it into a valid reference.
• If INDIRECT can't understand the text as a reference, it will return a #REF error.
• INDIRECT can cause performance problems in large or complex worksheets. Use with care.

Here are a few ways you can use the INDIRECT function in a formula:

• Create a formula that uses a sheet name entered in a cell.
• Create a lookup formula with a variable lookup table.
• A formula that can assemble a cell reference from bits of text
• Create a fixed reference that will not change even when rows or columns are deleted
• Create numeric arrays with the ROW function in older versions of Excel.

Example 1 - the basic idea of INDIRECT

The worksheet below shows the basic idea of the INDIRECT function. The text entered in column E represents different ranges. However, if we try to use the text directly in the SUM function as a range, SUM returns zero:

This happens because SUM doesn't see the text value as a reference; it simply sees a text string:

=SUM(E6)
=SUM("C5:C6")
=0

The solution is to add the INDIRECT function, which converts the text values into actual ranges:

Notice in the second line below we still have a text value, but in the third line we have the range C5:C6, and SUM now returns 9:

=SUM(INDIRECT(E6))
=SUM(INDIRECT("C5:C6"))
=SUM(C5:C6)
=9

Example 2 - Variable worksheet name

In the example shown below, INDIRECT is set up to use a variable sheet name. The formula in cell C5 is:

=INDIRECT(B5&"!A1") // sheet name in B5 is variable

The formula in C5 concatenates the text in B5 to the string "!A1" and returns the result to INDIRECT. The INDIRECT function then evaluates the text and converts it to a valid reference. As the formula is copied down, it returns the value in cell A1 for each of the 5 sheets listed in column B.

The formula is dynamic and responds to the sheet names in column B. If the sheet names are changed, the formula will automatically recalculate.

If the sheet names in your worksheet include spaces or punctuation, use the formula below:

=INDIRECT("'"&B5&"'!A1") // single quotes added

Example 3 - INDIRECT with a dropdown list

Using the same approach explained in the example above, we can allow a user to select a sheet name from a dropdown list, and then construct a reference to cell A1 on the selected sheet with INDIRECT. The formula in cell C5 is the same:

=INDIRECT(B5&"!A1") // sheet name from dropdown

When a different sheet name is selected, the formula will recalculate and INDIRECT will fetch the value from cell A1 in the selected sheet. Note that cell A1 is used only as an example, you can change the cell reference as desired.

Example 4 - Variable lookup table

In the worksheet below, VLOOKUP is used to get costs for two vendors, A and B. Using the vendor indicated in column F, VLOOKUP automatically uses the correct table:

The formula in G5 is:

=VLOOKUP(E5,INDIRECT("vendor_"&F5),2,0)

Read a full explanation here.

Example 5 - Fixed reference

Normally, a reference like A1:A100 will change if rows or columns are deleted. For example, if a row is deleted in this range, the reference will become A1:A99. To create a reference that will not change, you can use the INDIRECT function like this:

=INDIRECT("A1:A100") // fixed reference

Because the text value is static, the reference created by INDIRECT will not change even when cells, rows, or columns are inserted or deleted. The formula below will always refer to the first 100 rows of column A.

Example 6 - named range

The INDIRECT function can easily be used with named ranges. The worksheet below contains two named ranges: Group1 (B5:B12) and Group2 (C5:C12). When "Group1" or "Group2" is entered in cell F5, the formula in cell F6 sums the appropriate range using INDIRECT like this:

=SUM(INDIRECT(F5))

The value in F5 is text, but INDIRECT converts the text into a valid range.

A specific example of this approach is using named ranges to make dependent dropdown lists.

Example 7 - Generate a numeric array

A more advanced use of INDIRECT is to create a numeric array with the ROW function like this:

ROW(INDIRECT("1:10")) // create {1;2;3;4;5;6;7;8;9;10}

One use case is explained in this formula, which sums the bottom n values in a range. You may also run into the ROW + INDIRECT approach in more complex formulas that need to assemble a numeric array "on the fly". One example is this formula, designed to strip numeric characters from a string.

Troubleshooting INDIRECT

Working with the INDIRECT function can be tricky because you can't actually see the reference it returns. Instead, you just see the value at the reference when it works, or an error if the reference is invalid. Here are some troubleshooting tips:

• Be sure you have a good understanding of How to concatenate in Excel. Many INDIRECT problems are caused by text values that can't be coerced into valid reference.
• Be sure to include single quotes when referencing sheet names that contain space or punctuation (i.e. 'Sheet 1'!A1).
• Debug the text string being delivered to INDIRECT with the F9 key to confirm it meets expectations.
• Work in small steps to make sure INDIRECT is returning the reference you expect before plugging it into a more complex formula.

Notes

• References created by INDIRECT are evaluated in real-time and the value at the reference is returned.
• When ref_text is an external reference to another workbook, the workbook must be open.
• When a1 is TRUE (the default value), INDIRECT evaluates ref_text as an "A1" style reference.
• When a1 is FALSE, INDIRECT evaluates ref_text as an "R1C1" style reference.
• INDIRECT is a volatile function and can cause performance issues in large or complex worksheets.