In this article, you will get to know some of the examples of indirect address in Excel. By using an indirect address, you will be able to refer to the address of the cell rather than to the cell itself. So, let’s get started with the main article.

**Table of Contents**hide

## Download Workbook

## 12 Examples of INDIRECT Address in Excel

Here, we have used the following table for demonstrating the examples of the indirect address in Excel.

For creating the article, we have used *Microsoft Excel 365* version, you can use any other versions according to your convenience.

### 1. Using INDIRECT Function for Indirect Referencing

Here, we have two tables and we want to have the values of the sales of the first table in the second table in the **Sales **column. So, we can paste these values with indirect address referencing by using the **INDIRECT function**.

➤Select the output cell **F5**➤Type the following formula

`=INDIRECT("C"&ROW(C5))`

**ROW(C5)**→ returns the row number of cell**C5****Output**→ 5

**INDIRECT(“C”&ROW(C5))**becomes**INDIRECT(“C5”)**→ returns the value in cell**C5****Output**→ $4,629.00

➤Press **ENTER**➤Drag down the

**Fill Handle**Tool

** Result**:

In this way, you will get the values of the sales in the

**Sales column**of the second table by using indirect reference.

### 2. Adding up Values with Indirect Address Reference

Here, we will sum up the sales values by using indirect referencing.

➤Select the output cell **D9**➤Type the following formula

`=INDIRECT("D5")+INDIRECT("D6")+INDIRECT("D7")+INDIRECT("D8")`

**INDIRECT(“D5”)**→ returns the value in the cell**D5****Output**→ $4,629.00

**INDIRECT(“D6”)**→ returns the value in the cell**D6****Output**→ $3,257.00

**INDIRECT(“D7”)**→ returns the value in the cell**D7****Output**→ $2,091.00

**INDIRECT(“D8”)**→ returns the value in the cell**D8****Output**→ $2,125.00

**INDIRECT(“D5”)+INDIRECT(“D6”)+INDIRECT(“D7”)+INDIRECT(“D8”)**→ becomes

$4,629.00+$3,257.00+$2,091.00+$2,125.00**Output**→ $12,102.00

➤Press **ENTER**

** Result**:

After that, you will get the sum of sales in the

**D9**cell.

### 3. Indirect Address with Partial Text and Partial Cell Reference

Here, we will use an indirect reference with partial text and cell reference to paste the sales values in the **Sales **column of the second table. For this purpose, we have added a column named **Row No. **which contains the row numbers of the sales values.

➤Select the output cell **G5**➤Type the following formula

`=INDIRECT("C"&$D5)`

**$D5**→ returns the value in cell**D5****Output**→ 5

**INDIRECT(“C”&$D5)**becomes**INDIRECT(“C5”)**→ returns the value in cell**C5****Output**→ $4,629.00

➤Press **ENTER**➤Drag down the

**Fill Handle**Tool

** Result**:

Then, you will get the values of the sales in the **Sales column **of the second table by using indirect reference.

### 4. Indirect Address for a Named Range

Here, we have the name **sales1 **for the range of the **Sales **column and by using the **INDIRECT function** we will paste the sales values in the **Sales **column of the second table.

➤Select the output cell **F5**➤Type the following formula

`=INDIRECT("sales1")`

**sales1 **is the name of the **Sales **range.

➤Press **ENTER**

** Result**:

After that, you will get the values of the sales in the

**Sales column**of the second table by using an indirect address.

### 5. Adding up Values for a Named Range

Here, we will sum up the sales values by using an indirect address reference.

➤Select the output cell **D12**➤Type the following formula

`=SUM(INDIRECT("sales2"))`

**sales2 **is the name of the **Sales **range.

**INDIRECT(“sales2”)**returns the values of the**Sales**range**SUM**will sum up the values of the range.

➤Press **ENTER**

** Result**:

Afterward, you will get the sum of sales in the

**D12**cell.

### 6. Making a List with ROW and INDIRECT Function

In this section, we will get the row numbers in the **Row No. **column by using the **INDIRECT function **and the **ROW function**.

➤Select the output cell **E5**➤Type the following formula

`=ROW(INDIRECT("5:11"))`

**INDIRECT(“5:11”)**returns**$5:$11****ROW(INDIRECT(“5:11”))**becomes**ROW($5:$11)****Output**→ {5;6;7;8;9;10;11}

➤Press **ENTER**

** Result**:

In this way, you will get the list of the row numbers.

### 7. Using INDIRECT Function and ADDRESS Function for Indirect Referencing

Here, we want to have the values of the sales of the first table in the second table in the **Sales **column. So, we can paste these values with indirect address referencing by using the **INDIRECT function **and the **ADDRESS function**. Besides these, we will use the row numbers of the **Row No. **column.

➤Select the output cell **G5**➤Type the following formula

`=INDIRECT(ADDRESS(D5,3))`

**D5**→ returns the value in the cell**D5****Output**→ 5

**ADDRESS(D5,3)**becomes**ADDRESS(5,3))**→ returns the cell address**Output**→ $C$5

**INDIRECT(ADDRESS(D5,3))**becomes**INDIRECT(“$C$5”)****Output**→ $4,629.00

➤Press **ENTER**➤Drag down the

**Fill Handle**Tool

** Result**:

Then, you will get the values of the sales in the

**Sales column**of the second table by using indirect reference.

### 8. Using INDIRECT Function and ADDRESS Function for Referring Total Value

Here, we want to refer to the total value in cell **E6 **and we can do this by using a simple reference **$D$12** but if any other rows are added or deleted then this reference will give us a wrong value. By using indirect address reference we will refer to this total value in such a way that it will give us the right value always. Moreover, we have named the range of the **Sales **column as **sales3**.

➤Select the output cell **E6**➤Type the following formula

`=INDIRECT(ADDRESS(ROW(D4) + ROWS(sales3), COLUMN(sales3)))`

**ROW(D4)**→ returns the row number of the cell**D4****Output**→ 4

**ROWS(sales3))**becomes**ROWS($D$4:$D$11)**→ returns the number of rows in this array**Output**→ 8

**COLUMN(sales3)**becomes**COLUMN($D$4:$D$11)**→ returns the column number of this reference**Output**→ 4

**ADDRESS(ROW(D4) + ROWS(sales3), COLUMN(sales3))**becomes**ADDRESS(4 + 8, 4)**→**ADDRESS(12, 4)****Output**→ $D$12

**INDIRECT(ADDRESS(ROW(D4) + ROWS(sales3), COLUMN(sales3)))**becomes**INDIRECT($D$12)****Output**→ $23,084.00

➤Press **ENTER**

** Result**:

After that, you will get the total value in your desired cell

**E6**

### 9. Indirect Address of Cells From Another Sheet

Here, we have three different sheets named **January**, **February, **and **March **and each of them contains the sales of the products.

Now, we will paste the sales values from these sheets in the following table in the corresponding column of these months by using indirect address reference.

➤Select the output cell **C5**➤Type the following formula

`=INDIRECT("January!"&ADDRESS(ROW(D5),COLUMN(D5)))`

**ROW(D5)**→returns the row number of the cell**D5****Output**→ 5

**COLUMN(D5)**→returns the column number of the cell**D5****Output**→ 4

**ADDRESS(ROW(D5),COLUMN(D5))**becomes**ADDRESS(5,4)****Output**→$D$5

**INDIRECT(“January!”&ADDRESS(ROW(D5),COLUMN(D5)))**becomes**INDIRECT(“January!”&”$D$5”)**→**INDIRECT(“January!$D$5”)****Output**→$4,629.00

➤Press **ENTER**➤Drag down the

**Fill Handle**Tool

Then, you will get the sales record of *January *month from the *January *sheet in the **January **column.

Similarly, you can get the sales record for **February **and **March **by using the following formulas

`=INDIRECT("February!"& ADDRESS(ROW(D5),COLUMN(D5)))`

`=INDIRECT("March!"& ADDRESS(ROW(D5),COLUMN(D5)))`

### 10. Using INDIRECT Function and VLOOKUP Function

The table for the fruits is named as **jan_Fruit **and the range for the vegetables is named as **jan_Vegetable**. Here, we look for the sales value for **Strawberry** and **Tomato** by using the **INDIRECT function **and the **VLOOKUP function**.

➤Select the output cell **G5**➤Type the following formula

`=VLOOKUP(E5,INDIRECT("jan_"&F5),2,FALSE)`

Here, **E5 **is *Strawberry*

**INDIRECT(“jan_”&F5)**becomes**INDIRECT(“jan_Fruit”)****Output→**$B$4:$C$8

**VLOOKUP(E5,INDIRECT(“jan_”&F5),2,FALSE)**becomes**VLOOKUP(E5,$B$4:$C$8,2,FALSE)****Output→**$3,257.00

➤Press **ENTER**➤Drag down the

**Fill Handle**Tool

** Result**:

In this way, you will get the sales values for

*Strawberry*and

*Tomato*.

### 11. Calculation of Average with Indirect Address Referencing

Here, we will calculate the average of the smallest three sales values of the **Sales **column by using an indirect address reference. For this purpose, we will use the **AVERAGE function**, the **SMALL function**, the **ROW function**, and the **INDIRECT function**.

➤Select the output cell **G5**➤Type the following formula

`=AVERAGE(SMALL(D5:D11,ROW(INDIRECT("1:3"))))`

**ROW(INDIRECT(“1:3”))→ROW($1:$3)****Output→**{1;2;3}

**SMALL(D5:D11,ROW(INDIRECT(“1:3”)))**becomes**SMALL(D5:D11,{1;2;3})→**returns the smallest 3 values in the range D5:D11**Output→**{2091;2125;2167}

**AVERAGE(SMALL(D5:D11,ROW(INDIRECT(“1:3”))))**becomes**AVERAGE({2091;2125;2167})****Output→**2127.67

➤Press **ENTER**

** Result**:

Finally, you will get the average sales

### 12. Calculation of Running Total with INDIRECT Function

You can also use indirect reference for calculating the running total of sales easily by locking the first cell of the **Sales **column with the help of the **INDIRECT function**.

➤Select the output cell **E5**➤Type the following formula

`=SUM(INDIRECT("D5"):D5)`

**INDIRECT(“D5”)→**$D$5**SUM(INDIRECT(“D5”):D5)**becomes**SUM($D$5:D5)→ SUM**will return the sum of the values of this range**Output**→ $4,629.00

➤Press **ENTER**➤Drag down the

**Fill Handle**Tool

** Result**:

Then, you will get the running total of the sales.

## Practice Section

For doing practice by yourself we have provided a** Practice** section like below in a sheet named **Practice**. Please do it by yourself for a better understanding of indirect address in Excel.

## Conclusion

In this article, we tried to cover some of the examples of the indirect address in Excel. Hope you will find it useful. If you have any suggestions or questions, feel free to share them in the comment section.