Dynamic ranges refer to flexible ranges that automatically expand as the data grows. Let’s explore their usefulness and how to create them in Google Sheets.

You can refer to a range in a formula in two common ways:

• As an open range, such as A:A or A1:A, which refers to all or part of the column.
• As a closed range, such as A1:A100, which refers to a fixed number of rows.

A dynamic range refers to a range that starts at a specific point, like A1 and extends to the last populated row in the column. The range automatically expands as you add more data. We can create such a range without relying on any helper cells.

Purpose

Here are the two main purposes:

1. Improving formula performance by limiting the range to available data.
   Assume you have data in A1:B5, where A1:A5 contains items and B1:B5 contains quantities. If you want to sum the quantities in the range B2:B5, you can use =SUM(dynamic_range). When you add data in B10, the formula will automatically sum from B2 to B10 without needing to change the range manually. You will notice performance improvements when using dynamic ranges in functions such as FILTER, QUERY, etc.
2. Limiting the array formula expansion to the last non-empty cell in the range.
   When using functions such as VLOOKUP and SUMIF in an array context, you may encounter trailing zeros or #N/A errors in empty rows. You can avoid this by using dynamic ranges in these functions.

As a side note, while SUMIF requires a physical range in the sum_range argument, our suggested method will work without any issues.

Creating Dynamic Ranges in Google Sheets

Let’s first create a dynamic range for column B, and then we’ll explore how to use it for different purposes.

=INDEX(B1:B, 1):INDEX(B1:B, XMATCH(TRUE, B1:B<>"", 0, -1))

This formula creates a dynamic range for column B. It starts at B1 and extends to the last non-empty cell in the column.

If you want to create a dynamic range starting from cell C10, replace B1:B with C10:C in the formula.

If you want to sum the range from B2 to the last non-empty cell, you can use the dynamic range directly within the SUM function, eliminating the need for any helper cells:

=SUM(
   INDEX(B2:B, 1):INDEX(B2:B, XMATCH(TRUE, B2:B<>"", 0, -1))
)

Here’s another example. After this, we’ll dive into the explanation of dynamic ranges:

=SUMIF(
   INDEX(A2:A, 1):INDEX(B2:B, XMATCH(TRUE, B2:B<>"", 0, -1)), 
   "Papaya", 
   INDEX(B2:B, 1):INDEX(B2:B, XMATCH(TRUE, B2:B<>"", 0, -1))
)

This formula sums the values in the range B2:B (up to the last non-empty cell) wherever the corresponding values in A2:A (up to the last non-empty cell) match “Papaya.”

In this SUMIF formula, both range and sum_range are dynamic ranges. The formula follows the syntax SUMIF(range, criterion, [sum_range]).

Where:

• range: INDEX(A2:A, 1):INDEX(B2:B, XMATCH(TRUE, B2:B<>"", 0, -1))
• criterion: "Papaya"
• sum_range: INDEX(B2:B, 1):INDEX(B2:B, XMATCH(TRUE, B2:B<>"", 0, -1))

Keep in mind that the first part of the reference should come from the corresponding column (e.g., A2:A in range and B2:B in sum_range), while the second part (the condition that determines the last row in the range) should be based on the column that defines the last row (in this case, B2:B).

Note: To create a multicolumn dynamic range, specify the range in the first part of the formula. For example, for the range A2:B, you can use the following formula:

=INDEX(A2:B, 1):INDEX(B2:B, XMATCH(TRUE, B2:B<>"", 0, -1))

This formula will reference the range from A2 to the last non-empty cell in column B.

Dynamic Range Explained

Assume you have values in A1:A100. Typically, you would reference this range in formulas as A1:A100, but you can refer to it differently.

For example, consider the following formula:

=INDEX(A1:A, 1):INDEX(A1:A, 100)

This formula references the range A1:A100. Here’s how it works:

• INDEX(A1:A, 1): This part refers to the first value in the range A1:A, which points to cell A1.
  The INDEX function in Google Sheets returns the value or reference of a cell at a specified row and column. In this case, A1:A is the range, and 1 refers to the first row in that range (which is A1).
• INDEX(A1:A, 100): This part refers to the hundredth value in the range A1:A, which points to cell A100.

When these two parts are combined with a colon (:) in between, they define a continuous range from A1 to A100. This is equivalent to writing A1:A100.

Now, to create a dynamic range, instead of hardcoding 100 in INDEX(A1:A, 100), we will use XMATCH to find the last non-blank row number in the column.

We’ll replace 100 with:

XMATCH(TRUE, A1:A<>"", 0, -1)

So the dynamic range formula becomes:

=INDEX(A1:A, 1):INDEX(A1:A, XMATCH(TRUE, A1:A<>"", 0, -1))

This formula will adjust to include data from A1 to the last non-blank cell in column A.

XMATCH Role:

The formula XMATCH(TRUE, A1:A<>"", 0, -1) is used to locate the last non-blank cell in the range A1:A.

Related: XMATCH First or Last Non-Blank Cell in Google Sheets

Syntax: XMATCH(search_key, lookup_range, [match_mode], [search_mode])

• search_key: TRUE
• lookup_range: A1:A<>"" evaluates to TRUE for non-blank cells and FALSE for blank cells. Normally, this kind of test would require ARRAYFORMULA in Google Sheets, but it’s not needed within the INDEX function.
• match_mode: 0 specifies an exact match.
• search_mode: -1 directs XMATCH to search TRUE in reverse order (from bottom to top) in the lookup_range, ensuring it finds the last non-blank cell.

Resources

• Offset Function in Google Sheets and Dynamic Ranges
• How to Use Dynamic Ranges in SUMIF Formula in Google Sheets
• How to Get Dynamic Range in Charts in Google Sheets
• Dynamic Ranges in GROWTH, TREND, and FORECAST Functions
• Average Each Row in Dynamic Range in Google Sheets
• Dynamic H&V Named Range in Google Sheets
• Proper Use of MMULT in Infinite Rows in Google Sheets