How to use INDIRECT function to create a reference range in Excel
How to use INDIRECT function to create a reference range in Excel: The INDIRECT function is used to change the reference to a cell within a formula without changing the formula itself. Follow the article below to learn more about using the INDIRECT function in Excel.
1. The structure of the INDIRECT function
Function syntax: =INDIRECT(ref_text; [a1])
Where:
- Ref_text: Required argument, which is a reference to a cell containing an A1-style or R1C1-style reference.
- A1: Optional argument, which is a logical value that specifies whether the ref_text argument is interpreted as an A1-style or R1C1-style reference. TRUE or omitted for A1-style, FALSE for R1C1-style.
Note:
- If ref_text refers to another Excel file, that file must be open. If the file is not open, the INDIRECT function returns the #REF! error value.
- If ref_text refers to a range outside the limits of rows 1,048,576 or columns 16,384, the INDIRECT function returns the #REF! error value.
- If a1 is TRUE or omitted, ref_text is interpreted as an A1-style reference.
- If a1 is FALSE, ref_text is interpreted as an R1C1-style reference.
2. How to use the INDIRECT function
For example, we have the following data table:
How to use INDIRECT function to create a reference range in Excel
If we set the formula to =INDIRECT(A2), the function will refer to cell A2 and return the result “B4”. Then the function will continue to refer to cell B4 and return the result “10”. Since “10” is not a reference type data, the function will stop and return the result of 10.
How to use INDIRECT function to create a reference range in Excel
If we set the formula to =INDIRECT(A5&D2), the function will refer to cell A5 and return the result “C”, and then refer to cell D2 and return the result “4”. Then the function will combine the two results to “C4” and continue to refer to cell C4 and return the result “11”. Since “11” is not a reference type data, the function will stop and return the result of 11.
How to use INDIRECT function to create a reference range in Excel
Therefore, this article has introduced to you how to use the INDIRECT function in Excel. Hopefully, through this article, you have a better understanding of how to use this function. Good luck!
