How to Use Excel INDIRECT Range (8 Easiest Ways)

To explain the INDIRECT function, we’ll use a dataset that represents sales information for 4 quarters. The dataset has 5 columns: Salesperson, Quarter-1, Quarter-2, Quarter-3, and Quarter-4.

Dataset of Excel INDIRECT Range


How to Use the Excel INDIRECT Range: 8 Easiest Ways

Method 1 – Using the INDIRECT Range to Get a Value Through a Cell Reference

We want to get the values of the cell range C4:C8 from Quarter-1.

  • In cell H4, insert the following formula:
=INDIRECT("C4:C8")

Using Excel INDIRECT Function Range to Get Value Through Cell Reference

In the INDIRECT function, we used the cell range C4:C8 as a string/text (“C4:C8”) in ref_text.

  • Press Enter and you will get all the values of the used cell references.


Method 2 – Get a Value Through a Cell Reference in a Cell 

Let’s get the values of the cell range D4:D8 from Quarter-2. Instead of using the cell reference directly, we want to keep it in a cell. In the dataset given below, we placed the cell range in a separate cell.

Get Value Through Cell Reference in Cell Using INDIRECT Range

  • In cell I4, insert the following formula:
=INDIRECT(H4)

In the INDIRECT function, we selected cell H4 as ref_text. This is where we stored the cell range D4:D8 in the H4 cell, so it will fetch the values of those cells.

  • Press Enter.

Get Value Through Cell Reference in Cell Using INDIRECT Range

Read More: How to Use the INDIRECT Function to Get Values from Different Sheet in Excel


Method 3 – Using a Value in a Cell for Creating a Reference

  • In cell I4, use the following formula.
=INDIRECT("E"&H4)

Using Value in a Cell Creating Reference Using Excel INDIRECT

Here, in the INDIRECT function, we used “E”&H4 as ref_text. E is the column and in H4 we put the value 5. In the function, I concatenated column E and the value of the H4 cell so that it will become E5. Finally, the INDIRECT function will fetch the value of the E5 cell.

  • Press ENTER and you will get the values from the cell you used as a reference.


Method 4 – Find Out the SUM of a Range of Cells Using a Named Range

  • Select a cell range. We selected the cell range C4:C8.
  • Go to the Address Bar and type any name of your choice to name the range. We named the selected range Quarter_1.

Using Value in a Cell Creating Reference

  • In cell I4, use the following formula:
=SUM(INDIRECT(I3))

Using Excel INDIRECT and SUM function together

Here, in the SUM function, we used INDIRECT(I3) as a number1. In the INDIRECT function, we selected cell I3 as ref_text, where we put the name of the range C4:C8. The INDIRECT function will get the values of those cells and the SUM function will return the total of those cells.

  • Press Enter and you will get total sales of Quarter_1.


Method 5 – Using the INDIRECT with a Named Range to Find the AVERAGE 

  • In cell I4, use the following formula:
=AVERAGE(INDIRECT(I3))

Using Excel INDIRECT Range to Find Out The AVERAGE of Range of Cells

In the AVERAGE function, we used INDIRECT(I3) as a number1. For the INDIRECT function, we selected cell I3 as ref_text, which contains the name of the range C4:C8. The INDIRECT function will get the values of those cells and the SUM function will return the average of those cells.

  • Press Enter and you will get the average sales of Quarter_1.


Method 6 – Find the MAXIMUM of a Range of Cells

  • In cell I4, use the following formula:
=MAX(INDIRECT(I3))

Using Excel INDIRECT Range to Find Out The MAXIMUM of a Range of Cells

In the MAX function, we used INDIRECT(I3) as a number1. For the INDIRECT function, we used cell I3 as ref_text, which contains the name of the range C4:C8. The INDIRECT function will get the values of those cells and the MAX function will return the maximum number from those cells.

  • Press Enter and you will get the maximum sales of Quarter_1.


Method 7 – Find Out the MINIMUM of Range of Cells

  • In cell I4, use the following formula:
=MIN(INDIRECT(I3))

Using Excel INDIRECT Range to Find Out The MINIMUM of Range of Cells

In the MIN function, we used INDIRECT(I3) as a number1. For the INDIRECT function, we selected cell I3 as ref_text, which holds the name of the range C4:C8. The INDIRECT function will get the values of those cells and the MIN function will return the minimum number from those cells.

  • Press Enter and you will get the minimum sales of Quarter_1.


Method 8 – Using the Excel INDIRECT Range to Find the AVERAGE of SMALL Values

  • In cell I4, copy the following formula:
=AVERAGE(SMALL(F4:F8,ROW(INDIRECT("1:3"))))

Using Excel INDIRECT Range to Find Out The AVERAGE & SMALL Values

In the AVERAGE function, we used SMALL(F4:F8,ROW(INDIRECT(“1:3”))) as a number1. For the SMALL function, we used F4:F8 as array and ROW(INDIRECT(“1:3”)) as k which is position. In the ROW function, we used INDIRECT(“1:3”) as a reference. In the INDIRECT function, we used 1:3 as ref_text so it fetches 3 values. Here, the INDIRECT function is used in the ROW function so that the formula remains correct even if you insert or delete any rows. The SMALL function will return the 3 smallest numbers and the AVERAGE function will return the average of those smallest numbers.

  • Press Enter and you will get the average of 3 smallest sales of Quarter_4.


Things to Remember

The INDIRECT function shows the #REF error if ref_text is not a valid cell reference and the range limit is exceeded.

The INDIRECT function will show the #NAME error if you misspell the function name.


Practice Section

Excel INDIRECT Range Practice Section


Download to Practice 


Related Articles


<< Go Back to Excel INDIRECT Function | Excel Functions | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Shamima Sultana
Shamima Sultana

Shamima Sultana, BSc, Computer Science and Engineering, East West University, Bangladesh, has been working with the ExcelDemy project for 2 years. She has written over 75+ articles and reviewed 1000+ articles for ExcelDemy. She has also led several teams with Excel VBA and Content Development works. Currently, she is working as the Project Manager and oversees the day-to-day work, leads the services team, allocates resources to the right area, etc. Her work and learning interests vary from Microsoft... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo