Home > Formulas > Lookup > INDIRECT Excel INDIRECT Function Examples & TipsMaster 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 ExamplesWatch 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
Thanks to Dave Peterson, for his contributions to this page. |
How INDIRECT Function WorksThe 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 ArgumentsThe INDIRECT function is in the Lookup category, and its syntax has two arguments: INDIRECT(ref_text,a1)
|
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,
|
Data Validation RuleThe INDIRECT function is used in the data validation settings dialog box, like the formula example shown below
The video below shows how to set this up. Video: Dependent Drop Down ListsIn 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 ExamplesHere are the written steps for the INDIRECT examples that are in the Introduction to INDIRECT video, shown above. 2) Lookup from Different Sheet 3) Refer To Different Workbook Tip: You can also use INDIRECT to compare the contents on different sheets |
Add INDIRECT FormulaNext, follow these steps to create an INDIRECT formula:
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 WorksIn 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.
The formula bar shows the sheet name in single quotes, then an exclamation mark, then the cell address:
|
Building the Text StringHere is the formula again, with the text string in bold red font:
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
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
Prevent ErrorsIf 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 WorkbookAn 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
Set up a Linked WorkbookNext, 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.
|
Get a Sample FormulaTo see the syntax that you'll need to use in your INDIRECT formula, follow these steps:
Create an INDIRECT formulaNext, 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.
|
How the Formula Works
|
Test the Formula
Formula NotesNote1: 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: |
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)
Cell reference -- refers to a cell that contains a text string
Combined -- Text string and Cell reference
|
R1C1 ExampleIn 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:
The formula in cell D2 refers to those cells, and adds the R character for row, and C character for column:
|
Get the Sample File
|
|
Excel Tip |
|
Technical Tip |
|
Warning |
Last updated: March 18, 2023 11:21 AM