How to use the INDIRECT function in Excel

The Excel INDIRECT function is a Lookup and Reference function, and its purpose is to return the reference specified by a text string. You can use the INDIRECT function when you want to change the reference in the cell within a formula without changing the formula itself. The formula and syntax for the INDIRECT function are below:

Formula and Syntax

INDIRECT (ref_text, [a1])

Ref_text: A reference to a cell that contains an A1-style reference, an R1C1-style reference, a name defined as a reference, or a reference to a cell as a text string. It is required. If ref_text is not a valid cell reference, the INDIRECT function will return the #REF! error.

 

How to use the INDIRECT function in Excel

Follow the steps on how to use the Excel INDIRECT function:

1. Launch Microsoft Excel.
2. Enter data into the spreadsheet or use existing data.
3. Select the cell you want to place the result
4. Enter the formula
5. Press Enter.

Launch Microsoft Excel.

Enter your data or use existing data.

Type into the cell where you want to place the result =INDIRECT(A1).

Press Enter to see the result. The result is 42.

The result is 42 because you are referencing the value in column D1, which is 42. See the photo above.

Now we are going to look up the current sales using the R1C1-style reference method.

Enter where you want to place the result; the formula is  =INDIRECT(“R7C”&COUNTA(7:7), FALSE).

The formula R represents the Row, which is Row 7, and the C represents the column.

We use the COUNTA function to count cells that contain information.

We input 7:7 in the formula because it is the row that contains the information that you want to look up.

Then we input False.

Press Enter to see the result, which is the current sale.

There are two other methods to use the INDIRECT function.

Method one is to click the fx button on the top left of the Excel worksheet.

An Insert Function dialog box will appear.

Inside the dialog box, in the section Select a Category, select Lookup and Reference from the list box.

In the section Select a Function, choose the INDIRECT function from the list.

Then click OK.

A Function Arguments dialog box will open.       

    

Type into the entry boxes the cell that contains the reference that you want to look up.

Then click OK.

Method two is to click the Formulas tab and click the Lookup and Reference button in the Function Library group.

Then select INDIRECT from the drop-down menu.

A Function Arguments dialog box will open.

Follow the same method in Method 1.

Then click OK.

We hope you understand how to use the INDIRECT function in Excel.

How does indirect function work in Excel?

The INDIRECT function returns the reference specified by a text string. If a1 is TRUE or omitted, ref_text is interpreted as an A1-style reference, and if a1 is FALSE, ref_text is interpreted as an R1C1-style reference.

How is indirect function used in data validation?

1. On the spreadsheet, select cell C3.
2. On the Ribbon, click the Data tab, then click Data Validation.
3. From the Allow drop-down list, choose List.
4. In the Source box, type an equal sign and INDIRECT function, referring to the first data cell in the Produce Type column:
5. Click OK.

READ: How to create Custom Excel Functions using VBA

How do you copy down an indirect formula?

In Microsoft Excel, you can copy formulas and place them elsewhere in the spreadsheet. Follow the steps below:

1. Double-click the cell which contains the INDIRECT formula.
2. Highlight the formula and right-click the formula and select Copy from the context menu.
3. Then select a cell in the spreadsheet and right-click and select Paste.

READ: How to write, build, and use VLOOKUP function in Excel.