You want to refer to a range of data without changing the formula in the cell. The following article introduces you to the INDIRECT function in Excel that helps you reference safe data without changing the formula in the cell.
INDIRECT function description
The I NDIRECT function makes a reference to a range of data or a range of data in Excel. Use the INDIRECT function when you want to reference a cell in a formula without changing the formula.
Syntax of the INDIRECT function
INDIRECT (ref_text, [a1])
Inside:
- ref_text: A reference to a range of data or a range of names.This reference can be either an A1 reference type or a R1C1 reference ( two ways to call a cell address), a defined name as a reference or a reference to cells as string text.
- a1: The logical value determining the type of reference contained in the text, is an optional parameter with the following values:
+ a1 = True or ignore -> Reference text is a reference of type A1 which is column A row 1.
+ a1 = False -> Reference text is a reference type R1C1 is Row 1 and Column 1.
Attention when using the INDIRECT function
- In case of referencing the ref_text data area to another working window, it is required that the window must be open otherwise the function returns the #REF! Error value .
- If the reference range exceeds the number of rows (greater than 1,048,576) or exceeds the number of columns (greater than 16,348 columns) => the function returns the #REF! Error value
INDIRECT function example
Example 1: Simple example to familiarize you with cell addresses to help you determine the type of reference.
For example, there are values in column B, using the function to get the values in column B starting from the 5th row with different reference types. In cells to calculate enter the formula: = INDIRECT (E5, TRUE)
Hit Enter to get the results. For R1C1 reference type, enter the same formula instead of the value a1 = False:
The result returns the correct value. With other values you do the same.
Example 2:
There is an order data sheet. For each item enter the selling price with the price data taken in 3 tables corresponding to 3 categories of goods:
Step 1: Name each data area of the type, for example with electronic items blackened data from B15: C17 move to the address bar to enter the name of the data area, paying attention to the name of the data area coincides with the name Types of rows in the data table above:
Step 2: Similar to the name of the data area for consumer goods, sewing products. After entering in the cell, you need to get the formula's selling price: = VLOOKUP (D5, INDIRECT (C5), 2,0)
Step 3: Press Enter, you have obtained the selling price in the sub-table into the main data table:
Similarly copying the formula for the remaining values results:
In addition to using the Indirect function, you can use the Vlookup function in combination with the if function:
The result returns the same value:
However, when the vlookup function combines the if statement, the statement will be longer than the Vlookup function combined with the Indirect function.
Above is how to use the INDIRECT function and some examples using the INDIRECT function hope to help you. Good luck!