What is INDIRECT Function in Excel?

INDIRECT function of Excel is used to give reference to the cell, cell range, any named range, work sheets or workbook. In simple words INDIRECT function is used to refer the cells, cell range of different work sheets or workbook in any formula and converts the text string into a valid cell reference. In this way, cell reference in a formula can be changed easily without changing the formula itself. 

This function is useful when data set is spread across multiple work sheets or workbooks and we want to use this data on another work sheet or workbook. 

Another advantage of INDIRECT function is that any indirect reference created through this function does not change when new rows/columns are added or deleted in a work sheet.

Where to find INDIRECT Function in Excel?

Formulas>Lookup & Reference>INDIRECT

How to use INDIRECT Function?

Let us understand how to use INDIRECT function in Excel.

Example 1

Suppose we have data of different football leagues and number of matches played by them in the below table:

and we want to convert the text in Teams column with valid cell reference. Let us check how INDIRECT function helps us in doing this.

Step 1: Select the cell in which you want to insert INDIRECT function. Open the INDIRECT function as explained above and following dialog box will appear.

Ref_text: is a cell reference or text string (or both) or named range.

A1: It is optional and a logical value that specifies the type of reference in Ref_text field.

A1-style       = TRUE or omitted

R1C1-style   = FALSE

A1-style is the reference type of Excel which refers to column followed by row number. For example, C5 refers to the cell at intersection of column C and row 5. Excel normally follows this reference style.

R1C1-style is the reference type of Excel which refers to row followed by column number. For example, R2C3 means row 2 and column 3 i.e. C2. In order to use this style, you must go to File>Options>Formulas>R1C1 and then check the R1C1 box.

As A1 is the usual reference style of the Excel we will use A1 in this tutorial.

Step 2: Select A9 in Ref_text field which is cell reference of A3 (which is again cell reference of our text string British League). Leave A1 field blank as we do not want to change our cell referencing style. 

You may notice that by putting A3 (the cell reference containing British League), INDIRECT function converts text string “British League” into valid cell reference.

Example 2

Let us take another example. We have three months sales data of different products and we want to use INDIRECT Function to get the sum of all its’ products sold for the month of January/February/March because INDIRECT reference created does not change with any addition/deletion of rows/columns whereas normal sum function does not include the cell range which are added after initial specified sum range. Let’s understand how the INDIRECT function helps in achieving that objective.

Step 1: Create name range for the all months (Formulas>Name Manager). Select the cell where you want to insert combine Sum & INDIRECT function.

You may notice that in case of INDIRECT function you have to only insert the cell reference of “January” and this function converts text string “January” into a valid reference B1.

Example 3

Suppose Winter Limited sales data for all the three months is spread across in three different work sheets and it wants to prepare a summary sheet showing sum of its three months sales. It wants to use combine Sum and INDIRECT function for this purpose as well. Let’s see how INDIRECT might be used in this situation.

Step 1 Cell range of the data set should be same for all the three separate sheets viz. January, February & March. In this example cell range B2:B5 contains the sales values for the month of January, February & March (in separate worksheet).

Step 2 Select the cell where you want to Sum the January sales using INDIRECT function.

Step 3 Insert the formula as shown below:

You may check that INDIRECT function converts the text January into a valid cell reference A2. Similarly, you can get the total for the month of February & March by just replacing A2 with A3 & A4 respectively in the above formula and the INDIRECT function gives you the total sales for these months.