[NEW] Excel INDIRECT Function | indirect – NATAVIGUIDES

What is the Excel INDIRECT Function?

The Excel INDIRECT Function returns a reference to a range. The INDIRECT function does not evaluate logical tests or conditions. Also, it will not perform calculations. Basically, this function helps lock the specified cell in a formula. Due to this, we can change a cell reference within a formula without changing the formula itself.

One advantage of this function is that the indirect references won’t change even when new rows or columns are inserted in the worksheet. Similarly, references won’t change when we delete existing ones.

 

Formula

=INDIRECT(ref_text, [a1])

 

Where:

ref_text is the reference supplied as text

a1 is the logical value

 

The type of reference, contained in the ref_text argument, is specified by a1.

When a1 is TRUE or is omitted, then ref_text is interpreted as an A1-style cell reference.

When a1 is FALSE, then ref_text is treated as an R1C1 reference.

A1 style is the usual reference type in Excel. It is preferable to use A1 references. In this style, a column is followed by a row number.

R1C1 style is completely opposite of A1 style. Here, rows are followed by columns. For example, R2C1 refers to cell A2 which is in row 2, column 1 in a sheet. If there is no number after the letter, then it means we are referring to the same row or column.

To learn more, launch our free Excel crash course now!

 

Example of Excel INDIRECT in action

Let’s understand the formula through an example. Suppose A1 = 32 and using the INDIRECT function, we give reference A1 as shown below:

 

 

In the above example, the INDIRECT function converted a text string into a cell reference. The INDIRECT function helps us put the address of one cell (A1 in our example) into another as a usual text string, and then get the value of the first cell by acknowledging the second.

 

How is the INDIRECT function useful to Excel users?

Yes, it is indeed a useful function. Let’s take a few examples to understand the merits of the INDIRECT function.

For an array of numbers:

 

 

When I give the formula Indirect(“D”&4), I will get 8. This is so as it will refer to D4.

 

 

When I use the formula INDIRECT(“E” & ROW() ), I used the EXCEL ROW function to return the reference to the current row number (i.e., 3), and used this to form part of the cell reference. Therefore, the Indirect formula returns the value from cell E3.

 

 

When I use the formula SUM( INDIRECT( “C4:E4” ) ), the Indirect function returns a reference to the range C4:E4, and then passes this to Excel’s SUM function. The SUM function, therefore, returns the sum of cells C4, D4, and E4, that is (4 + 8 + 9).

 

 

Similarly, when I use the formula AVERAGE ( INDIRECT( “C5:E5” ) ), the INDIRECT function returns a reference to the range C5:E5, and then passes this to Excel’s AVERAGE function.

 

 

The usefulness of Excel’s INDIRECT function is not just limited to building “dynamic” cell references. In addition, it can also be used to refer to cells in other worksheets.

To learn more, launch our free Excel crash course now!

 

How to lock a cell reference using INDIRECT function

Generally, when we add or delete rows or columns in Excel, the cell references change automatically. If you wish to prevent this from happening, the INDIRECT function is quite useful.

In the screenshot below, I used the INDIRECT function for A2 and gave a plain reference for A3.

 

 

 

When I insert a new column, what will happen? The cell equal to the logical operator still returns 20, as its formula was automatically changed to =B3.

However, the cell with the INDIRECT formula now returns 0, because the formula was not changed when a new row was inserted and it still refers to cell A1, which is currently empty:

 

 

 

So the Excel INDIRECT function would be useful in a scenario when we don’t want the formula to get changed automatically.

 

 

Free Excel Course

To keep learning about Excel functions and developing your skills, check out our Free Excel Crash Course!  Learn how to create more sophisticated financial analysis and modeling to become a successful financial analyst.

 

 

Additional resources

Thanks for reading CFI’s guide to the important Excel INDIRECT function! By taking the time to learn and master these functions, you’ll significantly speed up your financial analysis. To learn more, check out these additional CFI resources:

• Excel Functions for Finance

  Excel for Finance

  This Excel for Finance guide will teach the top 10 formulas and functions you must know to be a great financial analyst in Excel.

• Advanced Excel Formulas Course
• Advanced Excel Formulas You Must Know

  Advanced Excel Formulas Must Know

  These advanced Excel formulas are critical to know and will take your financial analysis skills to the next level. Download our free Excel ebook!

• Excel Shortcuts for PC and Mac

  Excel Shortcuts PC Mac

  Excel Shortcuts – List of the most important & common MS Excel shortcuts for PC & Mac users, finance, accounting professions. Keyboard shortcuts speed up your modeling skills and save time. Learn editing, formatting, navigation, ribbon, paste special, data manipulation, formula and cell editing, and other shortucts

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:

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.