Exceljet

Quick, clean, and to the point

Excel INDIRECT Function

Excel INDIRECT function
Summary 

The Excel INDIRECT function returns a valid cell reference from a given text string. INDIRECT is useful when you want to assemble a text value that can be used as a valid reference.

Purpose 
Create a reference from text
Return value 
A valid worksheet reference.
Syntax 
=INDIRECT (ref_text, [a1])
Arguments 
  • ref_text - A reference supplied as text.
  • a1 - [optional] A boolean to indicate A1 or R1C1-style reference. Default is TRUE = A1 style.
Version 
Usage notes 

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), the style is "A1". When a1 is FALSE, the style is "R1C1". For example:

=INDIRECT("A1") // returns reference like =A1
=INDIRECT("R1C1",FALSE) // returns reference like =R1C1

The purpose of INDIRECT may at first seem baffling (i.e. Why use text when you can just provide a proper reference?) but there are many situations where the ability to create a reference from text is useful, including:

  • A formula that needs a variable sheet name
  • A formula that can assemble a cell reference from bits of text
  • A fixed reference that will not change even when rows or columns are deleted
  • Creating numeric arrays with the ROW function in complex formulas

Note: INDIRECT is a volatile function and can cause performance problems in large or complex worksheets. Use with care.

Example #1 - Variable worksheet name

In the example shown above, INDIRECT is set up to use a variable sheet name like this:

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

The formula in B5, copied down, 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 into a proper reference. The results in C5:C9 are the values from cell A1 in 5 sheets listed in column B. 

The formula is dynamic in that responds to the values in column B. In other words, if a different sheet name is entered in column B5, the value from cell A1 in the new sheet is returned. With the same approach, you could allow a user to select a sheet name with a dropdown list, then construct a reference to the selected sheet with INDIRECT.

Note: sheet names that contain punctuation or space must be enclosed in single quotes ('), as explained in this example. This is not specific to the INDIRECT function; the same limitation is true in all formulas.

Example #2 - 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:

Example of INDIRECT for VLOOKUP with variable table

The formula in G5 is:

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

Read the full explanation here.

Example #3 - Fixed reference

The reference created by INDIRECT will not change even when cells, rows, or columns are inserted or deleted. For example, the formula below will always refer to the first 100 rows of column A, even if rows in that range are deleted or inserted:

=INDIRECT("A1:A100") // will not change

Example #4 - Generate 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 ROW + INDIRECT idea 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.

Notes

  • References created by INDIRECT are evaluated in real time and the content of the reference is displayed.
  • When ref_text is an external reference to another workbook, the workbook must be open.
  • a1 is optional. When omitted, a1 is TRUE = A1 style reference.
  • When a1 is set to FALSE, INDIRECT will create an R1C1-style reference.
  • INDIRECT is a volatile function, and can cause performance issues in large or complex worksheets.

Download 100+ Important Excel Functions

Get over 100 Excel Functions you should know in one handy PDF.