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: 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?
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: READ: How to write, build, and use VLOOKUP function in Excel.
