Buzz
Do you want to reference a data range without altering the formula in the cell? This article introduces you to Excel's INDIRECT function, ensuring safe data referencing without formula modifications.
Description of INDIRECT Function
The INDIRECT function in Excel performs references to a data range or cell. Use the INDIRECT function when you want to reference a cell within a formula without changing that formula.
Syntax of INDIRECT Function
INDIRECT(ref_text, [a1])
Where:
- ref_text: References a data range or range name. This reference can be in the form of A1 reference style or R1C1 reference style (representing cell addresses), named ranges, or text string references to cells.
- a1: Logical value determining the reference type contained in the text, an optional parameter with the following values:
+ a1 = True or omitted -> text reference is in A1 style, representing Column A, Row 1.
+ a1 = False -> text reference is in R1C1 style, representing Row 1 and Column 1.
Considerations when using INDIRECT function
- When referencing the data range ref_text to another workspace, it is mandatory for that workspace to be open; otherwise, the function returns an error value #REF!
- If the reference range exceeds the number of rows (greater than 1,048,576) or exceeds the number of columns (more than 16,348 columns) => the function returns an error value #REF!
Example of INDIRECT Function
Example 1: A simple illustration to familiarize you with cell addresses, helping you determine the reference type.
Example with values in column B, using a function to retrieve values in column B starting from the 5th row with various reference types. In the cell where you want to calculate, enter the formula: =INDIRECT(E5,TRUE)
Press Enter to get the result. For R1C1 reference type, enter a similar formula with a1=False:
The result returns the correct value. Repeat the same process for other values.
Example 2:
There's a simple order data table. For each item, input the selling price using data from 3 tables corresponding to 3 types of items:
Step 1: Name each data range for the item type. For example, for the black electronic item, data from B15:C17, move to the address bar, enter the data range name, ensuring it matches the item type name in the data table above:
Step 2: Similarly, name the data range for household items and computers. After entering, in the cell where you want to retrieve the selling price, enter the formula: =VLOOKUP(D5,INDIRECT(C5),2,0)
Step 3: Press Enter, and you have retrieved the selling price from the sub-table into the main data table:
Similarly, copy the formula for the remaining values to get the results:
In addition to using the Indirect function, you can employ the VLOOKUP function combined with the IF function:
The result returns similar values:
However, when using the VLOOKUP combined with the IF function, the statement becomes longer compared to VLOOKUP combined with Indirect.
Here is how to use the INDIRECT function and some examples, hoping to assist you. Wishing you all success!
