Home > Formulas > Lookup > INDIRECT

Excel INDIRECT Function Examples & Tips

Master the Excel INDIRECT function with these 4 practical examples. See how to create dynamic references, and dependent drop-down lists. Watch the short videos, see the written steps, get the sample Excel workbook.

Author: Debra Dalgleish

Video: INDIRECT Function Examples

Watch this short Excel tutorial video, to see how to use the INDIRECT function on its own, or combined with other Excel functions.

Written instructions are below the video. Download the sample INDIRECT workbook, to see the examples, and to follow along with the video.

Video Timeline

• 00:00 Introduction
• 00:14 Lock a Cell Reference
• 01:27 Refer to Named Range
• 02:50 Refer To Specific Sheet
• 04:22 Lock Array of Numbers

Thanks to Dave Peterson, for his contributions to this page.

How INDIRECT Function Works

The INDIRECT function is useful when you want to return a value, based on a text string. For example, select a range name from a drop down list, and get the total amount for the selected range.

In this screen shot, there is a drop down list in cell B2, where you can choose Actual or Budget. After you make a selection, the total for that type appears in cell B3.

INDIRECT Function Syntax Arguments

The INDIRECT function is in the Lookup category, and its syntax has two arguments: INDIRECT(ref_text,a1)

1. ref_text: A cell reference or a text string (or both), that create a range reference to a cell, range of cells or named range.
• For detailed examples for this argument, go to the Ref_Text Argument Examples section, further down on this page.
2. a1: (optional) Logical value - does the reference use A1 reference style?

• TRUE, or omitted -- ref_text must be A1 style reference
• FALSE -- ref_text must be R1C1 type of reference style

Dependent Drop Down Lists

The short video below, shows the steps, and there are detailed written steps, and sample files, on the Dependent Drop Down Lists page.

For example,

1. Select the Vegetable category in the Product Type column
2. The dependent drop down list in the Item column shows a list of Vegetable options.

Data Validation Rule

The INDIRECT function is used in the data validation settings dialog box, like the formula example shown below

• =INDIRECT(B3)

The video below shows how to set this up.

Video: Dependent Drop Down Lists

In this short video, you'll see how to set up a main drop down list, with a dependent drop down list in the next column, that uses the INDIRECT function.

There are written instructions, and a sample file to download, on the Dependent DropDown Lists Video page.

Intro to INDIRECT Video Examples

Here are the written steps for the INDIRECT examples that are in the Introduction to INDIRECT video, shown above.

1) Lock a Cell Reference

2) Lookup from Different Sheet

3) Refer To Different Workbook

4) Refer to a Named Range

Tip: You can also use INDIRECT to compare the contents on different sheets

Lock a Cell Reference

If you create a SUM formula, and new rows are inserted later, at the top of the list, the SUM formula might not include the new amounts. Here's how you can use the INDIRECT function to avoid that problem with your calculations.

The INDIRECT function can "lock" a specific cell in a formula. Then, if rows or columns are inserted or deleted above or to the left of that cell, those indirect references do not change. Without INDIRECT, the reference would automatically adjust.

To see how this works, follow these steps to create two formulas -- one with normal cell referencing, and one with an INDIRECT function combined with the SUM function.

1. In cells C2:C7, type a list of numbers
2. Copy the list into E2:E7
3. In cell C8, type a SUM formula: =SUM(C2:C7)
4. In cell E8, type this formula: =SUM(INDIRECT(“E2”):E7)

Insert a Row

To see the difference between the formulas, insert a blank row above row 2, and enter 100 for January, in cells C2 and E2.

• The total amount changes in column E, because the start cell is locked at E2.
• The total amount does NOT change in column C. The start cell shifts down to cell C3.

Lookup From Different Sheet

An INDIRECT formula can also refer to cells on other worksheets. In this example, you'll create a formula with the INDIRECT function, using references to a sheet name and cell name.

Note: In an Excel formula, some sheet names must be enclosed with single quotation marks, so our formula will add those.

Set Up the Test Data

Before building the INDIRECT formula, you'll set up two simple worksheets, as described below. The first sheet has 10 cells with sample data. The other sheet has test data to use in the INDIRECT formula.

1. First, add a blank sheet to the workbook, and name it, Data Sheet
2. On that sheet, enter a list of unique numbers, in cells A1:A10, to use as sample data
• The INDIRECT formula will return data from a specific cell in this list of numbers
3. Next, insert another blank sheet in the workbook, and name it Test.
4. On the Test sheet, in cell A2, type: Data Sheet
   • That is the sheet name where the list of numbers was entered
   • Be sure to type the sheet name exactly the same as it is on sheet tab
5. Finally, on the Test sheet, in cell B2, type a cell address from the range of numbers
   • In this example I used the address: A8

Add INDIRECT Formula

Next, follow these steps to create an INDIRECT formula:

1. On the Test sheet, in cell C2, type the following formula:
   =INDIRECT("'" & A2 & "'!" & B2)
2. Then, press the Enter key, to see the formula result -- the number from cell A8 on the Data Sheet worksheet.

To test the formula, type a different cell address in cell B2, to return a different number from the list on the Data Sheet.

NOTE: The second argument, ref_text, is not used, because A1 ref style is the default setting. To see this example in R1C1 style, go to the R1C1 Example, below.

How Formula Works

In this formula, only the first argument, ref_text, is used, in the INDIRECT function. We build the text string with the sheet name and cell reference, in the proper format to use in a formula.

• Tip: To see the required format, type an equal sign, then go to Data Sheet, and click cell A2

The formula bar shows the sheet name in single quotes, then an exclamation mark, then the cell address:

• 'Data Sheet'!A2

Building the Text String

Here is the formula again, with the text string in bold red font:

• =INDIRECT("'" & A2 & "'!" & B2)

The first item is a single quote. To refer to text in a formula, it goes inside double quotes.

• "'"

Next, to join things together in a text string, use the & (Ampersand) operator. There are 3 ampersands in this formula:

• &

Next, there is a reference to cell A2, which contains the sheet name

• A2

The next item is an exclamation mark. It is text, so it goes inside double quotes.

• "!"

The final item is a reference to cell B2, which contains the cell address for a number on the Data Sheet worksheet

• B2

Prevent Errors

If either cell A2 or B2 on the Test sheet is empty, the formula will return an error, because INDIRECT cannot create a valid cell reference with incomplete data..

To prevent this error, you can wrap the formula with an IF function, like the one shown below:

    =IF(OR(A2="",B2=""),"",INDIRECT("'" & A2 & "'!" & B2))    

With this formula, if A2 or B2 are empty, the formula result will be an empty string -- ""

Refer to Different Workbook

An INDIRECT formula can refer to cells in other workbooks, but will return a #REF! error if that workbook is closed. In this example, you'll create a formula with the INDIRECT function, using references to a file name, sheet name and cell name.

Set up a Data Workbook

1. Create a new Excel file named TestFile.xlsx
2. Change the first sheet name to Test Data
3. On the Test Data sheet, enter numbers in cells A1:A10
4. Save the workbook

Set up a Linked Workbook

Next, follow these steps, to create a workbook that will link to the data workbook. Or, get the download file, and go to the WkbkRef sheet.

1. Create another new Excel file, named LinkedFile.xlsx
2. On the first sheet, in cell A2, type the data file name, with or without file extension:  Test File
3. In cell A3, enter the sheet name: Test Data
4. In cell A4, type A7 -- we'll pull a number from that cell in the data workbook

Get a Sample Formula

To see the syntax that you'll need to use in your INDIRECT formula, follow these steps:

1. In the linked file, select cell A7, and type an equal sign:   =
2. Switch to Test File workbook, click on cell A7, and press Enter
3. In the formula bar, you'll see the reference that was created:
          ='[Test File.xlxs]Test Data'!$A$7
4. To keep that formula visible, type an apostrophe in front of the equal sign

Create an INDIRECT formula

Next, follow these steps to create an INDIRECT formula that uses the same syntax. The formula will include the single quote marks, square brackets and exclamation mark.

1. In cell A6, type the following formula:
   =INDIRECT("'[" & A2 & "]" & A3 & "'!" & A4)

How the Formula Works

1. The first part of the string is a single quote and square bracket, within a set of double quotes:
        " ' [ "  (I added spaces here, so it's easier to read). Single quotes are included in the string to prevent errors if the sheet name or file name contain space characters, like there are in this example
2. The & (ampersand) characters join all the parts of the text string together.
3. A2 is the cell that has the data file name
4. Next, there are characters to end the data workbook name -- a square bracket, within a set of double quotes:  " ] "  (spaces added for clarity)
5. A3 is the cell with the sheet name
6. Next, there are characters to end the data sheet name -- a single quote and exclamation mark, within a set of double quotes:   " ' ! "  (spaces added for clarity)
7. A4 is the cell with the data cell address.

Test the Formula

1. Change the cell address in cell A4, and the result in A6 will change.
2. After the INDIRECT formula is working, you can delete the sample link in cell A5

Formula Notes

Note1: If A2, A3 or A4 is empty, the formula will return an error. To prevent this, you can add an IF function:

     =IF(OR(A2="",A3="",A4=""),"", INDIRECT("'[" & A2 & "]" & A3 & "'!" & A4))

 Note2: If the Test File.xlsx workbook is closed, the INDIRECT formula will return a #REF error. I haven't used the following files, but they may help you if you need to pull data from a closed workbook:

• Harlan Grove wrote a UDF called PULL that will retrieve the value from a closed workbook. You can find the function at Harlan's download site:
  • https://www.4shared.com/file/L_eA8s4G/pull.html
• Laurent Longre has an add-in (morefunc.xll) at:
  • https://www.ozgrid.com/Services/ExternalFree.htm#MoreFunc
• It includes =indirect.ext() that may help you.

Refer to a Named Range

In addition to cell references, you can refer to named ranges in an INDIRECT formula. In this example, the INDIRECT function is used to sum the selected named range.

1. In cells A1:B5, type headings and numbers, as shown at right.
2. Name cells A2:A5 as East, and cells B2:B5 as West. There are naming instructions here:

   Names -- Naming Ranges

3. In cell D2, type the name of one of the ranges, e.g. East
4. In cell E2, type the formula: =SUM(INDIRECT(D2))
5. Press the Enter key, and the formula returns the sum of numbers in the East range.
6. Change cell D2 to West, and the formula returns the sum of numbers in the West range.

Ref_Text Argument Examples

In most cases, a cell reference or combination of text string and cell reference works best with the INDIRECT function.

Text string -- address is typed into the formula (not flexible)

• A1 style: =INDIRECT("A4") or =INDIRECT("Demo!B3") or =INDIRECT("Budget")
• R1C1 style: =INDIRECT("R4C1",FALSE)
• Note: If you use a simple text string, the range is "hard coded" in the formula, which has limited usefulness, except for locking a cell reference.

Cell reference -- refers to a cell that contains a text string

• A1 style cell reference: =INDIRECT(C6)
• R1C1 reference style: =INDIRECT(C8, FALSE)

Combined -- Text string and Cell reference

• A1 style: =SUM(INDIRECT("A4:A" & C12))

R1C1 Example

In the Reference to a Different Sheet example above, you could use an R1C1 format, instead of the default A1 reference style.

The A1 example has a cell address in cell B2, and the INDIRECT formula refers to that cell. The second argument is not used, because A1 is the default setting.

If you need to build the cell reference from a column number and a row number, you can use the second argument, and enter FALSE for that setting.

In the screen shot below:

• Row number is in cell B2
• Column number is in cell C2

The formula in cell D2 refers to those cells, and adds the R character for row, and C character for column:

• =INDIRECT("'" & A2 & "'!R" & B2 & "C" & C2,FALSE)

Get the Sample File

• INDIRECT Examples (for Video): Get the sample INDIRECT workbook that has examples used in the INDIREXT Examples video. The zipped file is in xlsx format, and does not contain any macros.