How to Use Indirect Address in Excel (12 Examples)

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.

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.

Indirect Address Excel


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.

Indirect Address Excel

➤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

indirect reference

➤Press ENTER
➤Drag down the Fill Handle Tool

indirect reference

Result:
In this way, you will get the values of the sales in the Sales column of the second table by using indirect reference.

indirect reference


2. Adding up Values with Indirect Address Reference

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

Indirect Address Excel

➤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

sum up

➤Press ENTER

Result:
After that, you will get the sum of sales in the D9 cell.

sum up


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.

Indirect Address Excel

➤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

partial text and cell reference

➤Press ENTER
➤Drag down the Fill Handle Tool

partial text and cell reference

Result:

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

partial text and cell 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.

Indirect Address Excel

➤Select the output cell F5
➤Type the following formula

=INDIRECT("sales1")

sales1 is the name of the Sales range.

indirect reference with named 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.

indirect reference with named range


5. Adding up Values for a Named Range

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

Indirect Address Excel

➤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.

add up with named range

➤Press ENTER

Result:
Afterward, you will get the sum of sales in the D12 cell.

add up with named range


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.

Indirect Address Excel

➤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}

making a list of row number

➤Press ENTER

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

making a list of row number


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.

Indirect Address Excel

➤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

indirect reference

➤Press ENTER
➤Drag down the Fill Handle Tool

indirect reference

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

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.

Indirect Address Excel

➤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

referring total value

➤Press ENTER

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

referring total value


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.

Indirect Address Excel

Indirect Address Excel

Indirect Address Excel

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.

referring from another sheet

➤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

referring from another sheet

➤Press ENTER
➤Drag down the Fill Handle Tool

referring from another sheet

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

referring from another sheet

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

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

referring from another sheet

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

 

referring from another sheet


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.

Indirect Address Excel

indirect address excel

➤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

VLOOKUP

➤Press ENTER
➤Drag down the Fill Handle Tool

VLOOKUP

Result:
In this way, you will get the sales values for Strawberry and Tomato.

VLOOKUP


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.

indirect address excel

➤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

average of smallest three values

➤Press ENTER

Result:
Finally, you will get the average sales

average of smallest three values


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.

indirect address excel

➤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

running total

➤Press ENTER
➤Drag down the Fill Handle Tool

running total

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

running total


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.

practice


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.

Tanjima Hossain

Hello everyone, This is Tanjima Hossain. I have completed my graduation from BUET. Then I have started working as a technical writer in SOFTEKO. I have grown interest in technical content writing, research topics, numerical analysis related field and so I am here. Besides this I love to interact with different people and I love to spend my spare time by reading, gardening ,cooking etc.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo