A dynamic range is a range of cells that automatically adjusts to the size and shape of the data. A dynamic range can be useful when you want to perform calculations, create charts, or apply conditional formatting to a changing data set.

One way to create a dynamic range is to use a formula that depends on a cell value. For example, you can use a cell value to specify the number of rows or columns in the range, or to filter the data based on a condition. This way, you can control the range by changing the cell value.

In this article, we will show you how to make a dynamic range based on cell condition in Excel 365 using Excel formula. We will explain the basic theory, the procedures, and a comprehensive example with real numbers.

To make a dynamic range based on cell condition, we need to use two functions: INDEX and MATCH.

The INDEX function returns a reference to a cell or a range of cells in a given array, based on the row and column numbers. For example, INDEX (A1:C5,2,3) returns the reference to the cell B3, which is the second row and third column of the array A1:C5.

The MATCH function returns the relative position of a value in a range or an array, based on a specified match type. For example, MATCH (10,A1:A5,0) returns 3, which is the position of the value 10 in the range A1:A5, using an exact match (0).

By combining these two functions, we can create a dynamic range that depends on a cell value. For example, suppose we have a table of sales data in A1:E10, and we want to create a dynamic range that includes only the rows where the sales amount is greater than a threshold value in cell G1. We can use the following formula:

=INDEX (A1:E10,MATCH (G1,D1:D10,1),1):INDEX (A1:E10,MATCH (G1,D1:D10,1)+COUNTIF (D1:D10,“>”&G1)-1,5)

This formula works as follows:

• The first INDEX function returns the reference to the first cell of the range, which is the cell in column A that corresponds to the first sales amount that is greater than or equal to the threshold value in G1. To find this cell, we use the MATCH function with the match type 1, which returns the largest value that is less than or equal to the lookup value. For example, if G1 is 500, the MATCH function returns 4, which is the position of the value 450 in D1:D10. Then, the INDEX function returns the reference to A4.
• The second INDEX function returns the reference to the last cell of the range, which is the cell in column E that corresponds to the last sales amount that is greater than the threshold value in G1. To find this cell, we use the MATCH function with the match type 1, and then add the count of the sales amounts that are greater than the threshold value, minus one. This way, we get the position of the last sales amount that meets the condition. For example, if G1 is 500, the MATCH function returns 4, and the COUNTIF function returns 3, which is the number of sales amounts that are greater than 500 in D1:D10. Then, we add 4 and 3, and subtract 1, to get 6. Then, the INDEX function returns the reference to E6.
• Finally, we use the range operator (:) to create a range from the first cell to the last cell, which is A4:E6 in this example.

Procedures

To make a dynamic range based on cell condition in Excel 365 using Excel formula, follow these steps:

• Identify the data range that you want to make dynamic, and the cell value that you want to use as a condition. For example, suppose you have a table of sales data in A1:E10, and you want to make a dynamic range that includes only the rows where the sales amount in column D is greater than a threshold value in cell G1.
• Enter the formula that combines the INDEX and MATCH functions in the Name Manager. To do this, go to the Formulas tab, click Name Manager, click New, enter a name for the dynamic range, such as SalesData, and enter the formula in the Refers to box. For example, enter the following formula:

=INDEX (A1:E10,MATCH (G1,D1:D10,1),1):INDEX (A1:E10,MATCH (G1,D1:D10,1)+COUNTIF (D1:D10,“>”&G1)-1,5)

• Click OK to save the name and close the Name Manager.
• Use the name of the dynamic range in your formulas, charts, or conditional formatting rules. For example, you can use the name SalesData to sum the sales amounts in the dynamic range, by entering the following formula:

=SUM (SalesData)

• Change the cell value that controls the dynamic range, and see how the range and the results update automatically. For example, change the value in G1 to 600, and see how the range changes to A5:E7, and the sum changes to 2100.

Example

To illustrate how to make a dynamic range based on cell condition in Excel 365 using Excel formula, let’s use a real example with the following table of sales data in A1:E10:

We want to create a dynamic range that includes only the rows where the sales amount is greater than a threshold value in cell G1. We will use the following formula to define the dynamic range:

=INDEX (A1:E10,MATCH (G1,D1:D10,1),1):INDEX (A1:E10,MATCH (G1,D1:D10,1)+COUNTIF (D1:D10,“>”&G1)-1,5)

We will name the dynamic range as SalesData, and use it to calculate the total sales amount and the average commission in the range. We will also create a chart that shows the sales amount by seller in the range.

The following screenshot shows the result when the value in G1 is 500:

As you can see, the dynamic range is A4:E6, which includes the rows where the sales amount is greater than 500. The total sales amount is 2700, and the average commission is 38.33. The chart shows the sales amount by seller for Carol, Dave, and Eve.

The following screenshot shows the result when the value in G1 is 1000:

As you can see, the dynamic range is A6:E10, which includes the rows where the sales amount is greater than 1000. The total sales amount is 6750, and the average commission is 67.5. The chart shows the sales amount by seller for Frank, Grace, Harry, Irene, and Jack.

Other Approaches

Besides using the INDEX and MATCH functions, there are other ways to make a dynamic range based on cell condition in Excel 365 using Excel formula. Here are some of them:

• Use the OFFSET and COUNTIF functions. For example, you can use the following formula to create the same dynamic range as before:

=OFFSET (A1,MATCH (G1,D1:D10,1)-1,0,COUNTIF (D1:D10,“>”&G1),5)

The OFFSET function returns a reference to a range that is offset from a given reference by a specified number of rows and columns, and has a specified height and width. In this formula, the reference is A1, the row offset is the position of the first sales amount that is greater than or equal to the threshold value minus one, the column offset is zero, the height is the count of the sales amounts that are greater than the threshold value, and the width is five.

• Use the FILTER function. For example, you can use the following formula to create the same dynamic range as before:

=FILTER (A1:E10,D1:D10>G1)

The FILTER function returns a filtered range that meets one or more criteria. In this formula, the range is A1:E10, and the criteria is that the sales amount in D1:D10.