Home > Formulas > Lookup > INDIRECT

Excel INDIRECT Function Examples & Tips

 

Debra Dalgleish - Contextures

See how to use the Excel INDIRECT function to create dependent drop-down lists, plus 4 more INDIRECT examples. Watch my short videos, see the written steps, and get the sample Excel file.

INDIRECT formula for dependent drop down list

1) Dependent Drop Downs with INDIRECT

My favourite way to use the INDIRECT function is in data validation, to create a dependent drop-down list. This technique makes it easier for people to find what they need, when entering data on an Excel worksheet.

dependent drop down list with INDIRECT function

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

2) 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.

3) How INDIRECT Function Works

The INDIRECT function is useful when you want to return a value, based on a text string. For example, in the Demo screen shot below:

  • From the drop down list in cell B2, choose a range name - Actual or Budget.
  • The SUM for the selected type appears in cell B3.

INDIRECT shows sum of selected data typs, Budget or Actual

3.1) INDIRECT Function Syntax Arguments

The INDIRECT function is in Excel's Lookup functions category.

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.
  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
    • a1 argument TRUE or FALSE
  ▶

 

In most cases, you don't need to use the second argument, [a1]

  • Most workbooks are displayed A1-style (row numbers and column letters) (TRUE)
  • If you omit the second argument, TRUE is the default setting

If your Excel workbook uses R1C1 style (row numbers and column numbers), see details in the R1C1 Example, further down this page.

4) INDIRECT Function Examples

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

4.1) Lock a Cell Reference

4.2) Lookup from Different Sheet

4.3) Refer To Different Workbook

4.4) Refer to a Named Range

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

4.1) Lock a Cell Reference

Problem: 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 automatically.

Solution: With the INDIRECT function, you can avoid that calculation problem!

  • 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, and ignore the new data

4.1.1) Create Test Formulas

To see how this works, you create two test SUM function formulas:

  1. one with normal cell referencing
  2. one with an INDIRECT function reference

To create the formulas, follow the steps below:

  • In cells C2:C7, type a list of numbers
  • Copy the list into E2:E7
  • In cell C8, type this simple SUM formula:
    • =SUM(C2:C7)
  • In cell E8, type this SUM / INDIRECT formula: 
    • =SUM(INDIRECT(“E2”):E7)

create two test formulas

4.1.2) Insert a Row

To see the difference between the formulas:

  • Insert a blank row above row 2
  • Enter 100 for January, in cells C2 and E2.

Now there are differences in the SUM results

  • 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.

INDIRECT locks cell reference

4.2) Lookup From Different Sheet

In the previous example, the INDIRECT function referred to cells on the same worksheet. However, an INDIRECT formula can also refer to cells on other worksheets.

In the example shown below:

  • The INDIRECT function refers to a specific cell address, on a different sheet.
  • To make the formula easy to maintain and adjust, we'll use input cells for the sheet name and cell address

4.2.1) Sample File Worksheets

In the sample file that you can download, there are 2 worksheets used for this example.

  1. Data Sheet
  2. SheetRef
a) Data Sheet
  • On the worksheet named Data Sheet, there is a list of numbers, from 1 to 10.
  • The numbers are listed in cells A1 to A10.

Data Sheet with list of numbers

b) SheetRef
  • At the top of the SheetRef sheet, there are input cells for a sheet name (Data Sheet), and cell address (A8)
  • The INDIRECT function will refer to those cells, to create the ref_text argument.
  • With input cells, it's easy to change the formula results later -- just type a different cell address to get a different number.

SheetRef sheet with sheet name and cell address input cells

4.2.2) Create a Test Link

For the first argument (ref_text) in the INDIRECT function, we need the sheet name and cell address, in a format that Excel can understand.

For a quick way to see that format, follow the steps below, to create a simple link formula.

  • First, in cell B6, type an equal sign
  • Next, go to the Data Sheet, and click on cell A8
  • Press the Enter key, to complete the formula

Select cell B6, and the formula bar shows the correctly formatted link with a sheet name and valid cell reference:

  • ='Data Sheet'!A8

simple link forrmula to different sheet

4.2.3) Add an INDIRECT Formula

To build a valid INDIRECT ref_text argument, you need to use the same reference structure.

For example, you could type the following INDIRECT formula into cell B6:

  • =INDIRECT("'Data Sheet'!A8")

It has the correctly formatted link, enclosed in double quotation marks:

Refer to Input Cells

However, instead of typing the link into the formula, I recommend using the input cells on the SheetRef worksheet.

  • In cell B6, enter the following formula:
    • =INDIRECT("'" & B3 & "'!" & B4)
  • Press the Enter key, to complete the formula

Cell B6 shows the value from cell A8.

  ▶

 

Note: The double quote marks are not needed in this formula, because the sheet name is in a cell, referenced in the formula.

 

get value from cell in different sheet

4.2.4) How the Formula Works

In this formula, the correctly formatted link is built, piece by piece.

  • =INDIRECT("'" & B3 & "'!" & B4)

Here are the pieces:

  1. "'" - Single quote mark. This is text, so it's in double quotes
  2. & - There are 3 Ampersand operators, to join the pieces together
  3. B3 - Input cell with the Sheet Name
  4. "'!" - Single quote mark and apostrophe, inside double quotes
  5. B4 - Input cell with Cell Address

4.3) Refer to Different Workbook

In this example, the INDIRECT formula refers to a cell in a different workbook.

  ▶   Warning: If the INDIRECT formula refers to a different workbook, that workbook must be open, or the formula will return a #REF! error. See the examples below, for referring to a different worksheet, or a different workbook.

4.3.1) 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

4.3.2) 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

4.3.3) 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

INDIRECT with other workbook - must be open

4.3.4) 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)

4.3.5) 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.

4.3.6) 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

4.3.7) 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:

4.4) 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.

INDIRECT with named range

5) Ref_Text Argument Examples

  ▶  

Tech Tip: This section shows 3 examples for the ref_text argument in the INDIRECT function. Keep reading if you'd like to learn more about these options, or skip to the next section.

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

5.1) 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.
5.2) Cell reference -- refers to a cell that contains a text string
  • A1 style cell reference: =INDIRECT(C6)
  • R1C1 reference style: =INDIRECT(C8, FALSE)
5.3) Combined -- Text string and Cell reference
  • A1 style: =SUM(INDIRECT("A4:A" & C12))

6) R1C1 Example

If your workbook uses R1C1 ref style (row numbers and column numbers), you'll need to use the [a1] argument for INDIRECT, and set it to FALSE.

For example, in the Reference to a Different Sheet example above, the workbook used A1 style.

get value from cell in different sheet

6.1) R1C1 Style

For a workbook that uses R1C1 ref style, use a different setup, as shown in the screen shot below:

  • Put the row number separately, in cell B2
  • Put the column number separately, in cell C2

The INDIRECT formula in cell D2 refers to those cells.

It also adds the R character for row, and C character for column:

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

get value from cell in different sheet

Get the Excel Files ✅

  • INDIRECT Examples: Get the sample INDIRECT workbook that has examples for the INDIRECT function. The zipped file is in xlsx format, and does not contain any macros.

Get Monthly Excel Tips!

Don't miss my monthly Excel newsletter! You'll get quick tips, article links, and a bit of fun. Add your email, and click Sign Up.

Next, when you get my reply, click the Confirm button. I add this step to protect you from spam!

Related Pages

Functions List

INDIRECT - Compare Sheets

VLOOKUP Function

CHOOSE Function

COUNT / COUNTIFS

INDEX / MATCH

 

 

 

Last updated: May 12, 2026 2:02 PM