How to Use Excel INDIRECT Function (Examples + Video)

Excel INDIRECT Function (Example + Video)

Excel INDIRECT Function

When to use Excel INDIRECT Function

Excel INDIRECT function can be used when you have the references as text and you want to get the values from those references.

What it Returns

It returns the reference specified by the text string.

Syntax

=INDIRECT(ref_text, [a1])

Input Arguments
  • ref_text – A text string that contains the reference to a cell or a named range.
  • [a1] – A logical value that specifies what type of reference to use for ref_text. This could either to be TRUE (indicating A1 style reference) or FALSE (indicating R1C1 type reference). If omitted, it is TRUE by default.
Additional Notes
  • INDIRECT is a volatile function (use with caution).
    • It recalculates whenever the excel workbook is open or whenever a calculation is triggered in the worksheet.
      • Since volatile functions re-calculates so often, it adds to the processing time and slows down your workbook.
  • The Reference Text (ref_text) could be:
    • A reference to a cell that in-turn contains a cell reference in A1-style or R1C1-style format.
    • A reference to a cell in double quotes.
    • A named range that returns a reference
Excel INDIRECT Function – Examples

Example 1: Use a Cell reference to Fetch the Value

Excel INDIRECT function takes the cell reference as input and returns the value in that cell reference (as shown in the example below):

Excel INDIRECT Function - Example 1

The formula in cell C1 is =INDIRECT(“A1”)

Excel INDIRECT function takes this cell references (within double quotes) and returns the value in this cell reference , which is 123.

Now if you’re thinking, why don’t I simply use =A1 instead of using the INDIRECT function, you have a valid question.

Here is why..

When you use =A1 or =$A$1, it gives you the same result. But when you insert a row above the first row, you would notice that the cell reference would automatically change to account for the new row.

Have a look at the demo below:

Excel INDIRECT function comes in handy when you want to lock the cell reference in such a way that it does not change when you insert rows/columns in the worksheet.

Example 2: Fetch a Cell Reference Using Excel INDIRECT Function

You can also use Excel INDIRECT function to fetch the value from a cell whose reference is stored in a cell itself.

Excel INDIRECT Function - Example 2

In the above example, cell A1 has the value 123.

Cell C1 has the reference to the cell A1.

Now when you use Excel INDIRECT function and use C1 as the argument (which in turn has a cell reference in it), the function would return the value in cell A1.

Note that you don’t need to use double quotes here as the C1 has the cell reference stored in the text format only.

Example 3: Using the Combination of Value and Text as Cell Reference in Excel INDIRECT Function

You can also create a cell reference using a combination of the column alphabet and the row number.

For example, if cell C1 contains the number 2, and you use the formula =INDIRECT(“A”&C1) then it would refer to cell A2.

Excel INDIRECT Function - Example 3

A practical application of this could be when you want to dynamically refer to cells based on the value in some other cell.

Example 4: Refer to a Range of Cells Using Excel INDIRECT Function

You can also refer to a range of cells the same way you refer to a single cell using the Excel INDIRECT function.

For example, =INDIRECT(“A1:A5”) would refer to the range A1:A5.

You can then use the SUM function to find the total or the LARGE/SMALL/MIN/MAX function to do other calculations.

Excel INDIRECT Function - Example 4

Example 5: Creating Reference to a Sheet Using Excel INDIRECT Function

The above examples covered how to refer a cell in the same worksheet. You can also use the Excel INDIRECT function to refer to a cell in some other worksheet as well.

Here is something you need to know about referring to other sheets:

  •  Let’s say you have a worksheet with the name Sheet1, and within the sheet in the cell A1, you have the value 123. If you go to another sheet (let’s say Sheet2) and refer to cell A1 in Sheet1, the formula would be: =Sheet1!A1

But..

  • If you have a worksheet that contains two or more than two words (with a space character in between), and you refer to cell A1 in this sheet from another sheet, the formula would be: =’Data Set’!A1

In case, of multiple words, Excel automatically inserts single quotation marks at the beginning and end of the worksheet name.

Now let’s see how to create an INDIRECT function to refer to a cell in another worksheet.

Suppose you have a sheet named Dataset and cell A1 in it has the value 123.

Now to refer to this cell from another worksheet, use the following formula:

=INDIRECT(“‘Data Set’!A1”)

As you can see, the reference to the cell needs to contain the worksheet name as well.

If you have the name of the worksheet in a cell (let’s say A1), then you can use the following formula:

=INDIRECT(“‘”&A1&”‘!A1”)

Excel INDIRECT Function - Example 5

If you have the name of the worksheet in cell A1 and cell reference in cell A2, then the formula would be:

=INDIRECT(“‘”&A1&”‘!”&A2)

Excel INDIRECT Function - Example 5b

Example 6: Referring to a Named Range Using Excel INDIRECT Function

If you have created a named range in Excel, you can refer to that named range using the INDIRECT function.

For example, suppose you have the marks for 5 students in three subjects as shown below:

Excel INDIRECT Function - Example 6

In this example, let’s name the cells:

  • B2:B6: Math
  • C2:C6: Physics
  • D2:D6: Chemistry.

To name a range of cells, simply select the cells and go to the name box, enter the name and hit enter.

Excel INDIRECT Function - named ranges

Now you can refer to these named ranges using the formula: =INDIRECT(“Named Range”)

For example, if you want to know the average of the marks in Math, use the formula: =AVERAGE(INDIRECT(“Math”))

If you have the named range name in a cell (F2 in the example below has the name Math), you can use this directly in the formula. The below example shows how to calculate the average using the named ranges.Excel INDIRECT Function - Example 6a

Example 7: Creating a Dependent Drop Down List Using Excel INDIRECT Function

This is one excellent use of the Excel INDIRECT Function. You can create a dependent drop down list using it.

For example, suppose you have a list of countries in a row and the name of cities for each country as shown below:

Excel INDIRECT Function - Example 6b

 

Now to create a dependent drop down list, you need to create two named ranges, A2:A5 with the name US and B2:B5 with the name India.

Now select cell D2 and create a drop down list for India and US. This would be the first drop-down list where the user gets the option to select a country.

Excel INDIRECT Function - Example 6c

Now to create a dependent drop-down list:

  • Select cell E2 (the cell in which you want to get the dependent drop down list).
  • Go to Data –> Data validation.Excel INDIRECT Function - data validation
  • Select List as the Validation Criteria and use the following formula in the source field: =INDIRECT($D$2)Excel INDIRECT Function - data validation indirect source
  • Click OK.

Now, when you enter US in cell D2, the drop down in cell E2 will show the states in the US.

Excel INDIRECT Function - dependent drop down list US

And when you enter India in cell D2, the drop down in cell E2 will show the states in India.

Excel INDIRECT Function - dependent drop down list India

Excel INDIRECT Function – Video Tutorial

Related Excel Functions:
Related Articles:
Other Excel Resources: