What is EXCEL INDIRECT FUNCTION
The Excel INDIRECT Function is listed under Microsoft Excel's Lookup Functions category. It returns a valid reference from a text string which represents a cell reference. In simple words, you can refer to a cell range by using the cell address as a text value.
How to use it
To learn how to use the INDIRECT function in Excel, you need to understand its syntax and arguments:
- ref_text: A text which represents the address of a cell, an address of a range of cells, a named range, or a table name. For example, A1, B10:B20, or MyRange.
- [a1]: A number or a boolean value to represent the type of cell reference you are specifying in ref_text. For example, if you want to use A1 reference style use TRUE or 1 and if you want to use R1C1 reference style use FALSE or 0 for R1C reference style. And if you omit to specify the cell reference type, it will use A1 style as default.
- When you referred to another workbook, that workbook should be opened.
- If you insert a row or a column in the range which you have referred, INDIRECT will not update that reference.
- If you want to insert text directly into the function you have to put it in double quotation marks or you can also refer to a cell that has the text you want to use as a reference.
To master the INDIRECT function we need to try it out in an example and below is one which you can try out:
1. Reference to Another Worksheet
You can also refer to another worksheet using the INDIRECT and you have to insert the worksheet name in it.
In the below example, we have used the indirect function to refer to another worksheet and have the sheet name in cell A2 and cell reference in cell B2.
In cell C2, we have used the following formula to combine the text.
This combination creates a text which is used by the INDIRECT function to refer to the cell A1 in sheet1 and the best part is when you change the worksheet name or cell address the reference will automatically change.
Cell A1 in “Sheet1” has the value “Yes” and that’s why indirect returns the value “Yes”.
2. Reference to Another Workbook
You can also refer to another workbook, in the same way, we did for another worksheet.
All you have to do, just add a workbook name in your text which you are using as a reference.
In the above example, we have used the following formula to get the value from the cell A1 of the workbook “Book1”.
As we have the workbook name in cell “A2”, worksheet name in cell “B2” and cell name in cell “C2”. We have combined them to use as an input text in indirect function.
Note: While combining cell reference as a text make sure to follow the right reference structure.
3. Using with Named Ranges
Yes, you can also refer to a named range using the indirect function.
It’s just simple. Once you create a named range you have to enter that named range as a text in INDIRECT.
In the above example, we have a drop-down in cell E1 which has a list of named ranges, and in cell E2 we have used that name.
As range B2:B5 is named as “Quantity” and range C2:C5 is named as “Amount”.
When you select quantity from drop-down indirect function instantly refers to the named range.
And when you select the amount from the drop-down, you will have the sum of cell range C2:C5.
This tutorial is the part of our Excel Functions with Examples (Function Guide) and below are some of the related functions:
- EXCEL ADDRESS Function
- EXCEL AREAS Function
- EXCEL CHOOSE Function
- EXCEL COLUMN Function
- EXCEL COLUMNS Function
- EXCEL FORMULATEXT Function
- EXCEL HLOOKUP Function
- EXCEL HYPERLINK Function
- EXCEL INDEX Function
- EXCEL LOOKUP Function
- EXCEL MATCH Function
- EXCEL OFFSET Function
- EXCEL ROW Function
- EXCEL ROWS Function
- EXCEL TRANSPOSE Function
- EXCEL VLOOKUP Function
About the Author
Puneet is using Excel since his college days. He helped thousands of people to understand the power of the spreadsheets and learn Microsoft Excel. You can ﬁnd him online, tweeting about Excel, on a running track, or sometimes hiking up a mountain.