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.

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")

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.

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.

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

In cell I4, use the following formula.

=INDIRECT("E"&H4)

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.

In cell I4, use the following formula:

=SUM(INDIRECT(I3))

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))

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))

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))

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"))))

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

Download to Practice

Ways to Use INDIRECT Range.xlsx

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

Get FREE Advanced Excel Exercises with Solutions!