INDIRECT function in Excel - How to use INDIRECT function and examples using INDIRECT function

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 in Excel - How to use INDIRECT function and examples using INDIRECT function Picture 1

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)

INDIRECT function in Excel - How to use INDIRECT function and examples using INDIRECT function Picture 2

Hit Enter to get the results. For R1C1 reference type, enter the same formula instead of the value a1 = False:

INDIRECT function in Excel - How to use INDIRECT function and examples using INDIRECT function Picture 3

INDIRECT function in Excel - How to use INDIRECT function and examples using INDIRECT function Picture 4

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:

INDIRECT function in Excel - How to use INDIRECT function and examples using INDIRECT function Picture 5

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:

INDIRECT function in Excel - How to use INDIRECT function and examples using INDIRECT function Picture 6

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)

INDIRECT function in Excel - How to use INDIRECT function and examples using INDIRECT function Picture 7

Step 3: Press Enter, you have obtained the selling price in the sub-table into the main data table:

INDIRECT function in Excel - How to use INDIRECT function and examples using INDIRECT function Picture 8

Similarly copying the formula for the remaining values ​​results:

INDIRECT function in Excel - How to use INDIRECT function and examples using INDIRECT function Picture 9

In addition to using the Indirect function, you can use the Vlookup function in combination with the if function:

INDIRECT function in Excel - How to use INDIRECT function and examples using INDIRECT function Picture 10

The result returns the same value:

INDIRECT function in Excel - How to use INDIRECT function and examples using INDIRECT function Picture 11

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!