How to Use the INDIRECT Function to Create Cell References in Excel
How to Use the INDIRECT Function to Create Cell References in Excel: The INDIRECT function is used to change the reference to a cell in a formula without changing the formula itself. Follow this article to learn more about using the INDIRECT function in Excel.
1. INDIRECT Function Structure
Function syntax: =INDIRECT(ref_text; [a1])
Where:
- Ref_text: Required argument, refers to a cell containing an A1-style or R1C1-style reference.
- A1: Optional argument, specifies whether the reference text is in A1-style or R1C1-style. A value of TRUE or omitted indicates A1-style, while a value of FALSE indicates R1C1-style.
Note:
- If ref_text refers to a different Excel file, that file must be open. If the file is not open, the INDIRECT function returns a #REF! error value.
- If ref_text refers to a cell outside the row limit of 1,048,576 or column limit of 16,384, the INDIRECT function returns a #REF! error value.
- If a1 is TRUE or omitted, the reference text is interpreted as A1-style reference. If a1 is FALSE, the reference text is interpreted as R1C1-style reference.
2. How to Use the INDIRECT Function
For example, suppose we have a data table like this:
How to Use the INDIRECT Function to Create Cell References in Excel
If we set the formula to =INDIRECT(A2), the function will refer to cell A2 and get the result “B4”. Then, the function will continue to refer to cell B4 and get the result “10”. Since “10” is not a reference data type anymore, the function will stop and return the result 10.
If we set the formula to =INDIRECT(A5&D2), the function will refer to cell A5 and get the result “C“. Then, it will refer to cell D2 and get the result “4“. Next, the function will combine the two results to get “C4”, and then refer to cell C4 and get the result “11”. Since “11” is not a reference data type anymore, the function will stop and return the result 11.
How to Use the INDIRECT Function to Create Cell References in Excel
In summary, this article has introduced how to use the INDIRECT function in Excel. Hopefully, through this article, you have a clearer understanding of how to use this function. Good luck!
