Exceljet

Quick, clean, and to the point

Combine ranges

Excel formula: Combine ranges
Summary 

To combine ranges in Excel with a formula, you can use formula primarily based on the INDEX function and the SEQUENCE function. In the example below, the formula in cell B5 is:

=IF(SEQUENCE(ROWS(r.1)+ROWS(r.2))<=ROWS(r.1),
INDEX(r.1,SEQUENCE(ROWS(r.1)+ROWS(r.2)),SEQUENCE(1,COLUMNS(r.1))),
INDEX(r.2,SEQUENCE(ROWS(r.1)+ROWS(r.2))-ROWS(r.1),SEQUENCE(1,COLUMNS(r.1)))
)

where r.1 (E5:F9) and r.2 (H5:I10) are named ranges. The formula appends the second range to the first range and the result spills into B5:C15. This is an example of how named ranges can make a complicated formula much easier to use, since the ranges only need to be defined once. However, the LET function also provides a way to streamline the formula. See below for details.

Explanation 

Although you can easily combine ranges in Excel with Power Query, it has traditionally been difficult to do with standard Excel formulas. However, the new dynamic array formulas in Excel 365 make it possible without too much complexity.

In this example, we are combining (also called appending or stacking) two ranges with a single formula in cell B5:

=IF(SEQUENCE(ROWS(r.1)+ROWS(r.2))<=ROWS(r.1),
INDEX(r.1,SEQUENCE(ROWS(r.1)+ROWS(r.2)),SEQUENCE(1,COLUMNS(r.1))),
INDEX(r.2,SEQUENCE(ROWS(r.1)+ROWS(r.2))-ROWS(r.1),SEQUENCE(1,COLUMNS(r.1)))
)

At the core, this formula figures out how many rows are in range 1 and range 2, and uses the SEQUENCE function to create a "row index" with the SEQUENCE function here:

SEQUENCE(ROWS(r.1)+ROWS(r.2)) // returns {1;2;3;4;5;6;7;8;9;10;11}

In a similar way, SEQUENCE is also used to create a "column index":

COLUMNS(r.1) // returns {1,2}

The IF function tests compares all values in the row index sequence with the row count for range 1. When a row index value is less than or equal to the count of the rows in range 1 (5), the INDEX function is used to fetch rows from range 1:

INDEX(r.1,SEQUENCE(ROWS(r.1)+ROWS(r.2)),SEQUENCE(1,COLUMNS(r.1)))

When a row index value is greater than 5, INDEX is used to fetch rows from range 2:

INDEX(r.2,SEQUENCE(ROWS(r.1)+ROWS(r.2))-ROWS(r.1),SEQUENCE(1,COLUMNS(r.1)))

This is a shortcut to keep things relatively simple. This formula does not try to figure out if the column counts for both ranges are the same or not. It simply assumes the column counts are the same for both ranges and uses range 1 to get an index for both.

With the LET function

The LET function can be used to make this formula somewhat easier to read. Below, we've adapted the formula to use LET, but kept the named ranges in place:

=LET(
rows1,ROWS(r.1),
rows2,ROWS(r.2),
cols1,COLUMNS(r.1),
rowindex,SEQUENCE(rows1+rows2),
colindex,SEQUENCE(1,cols1),
IF(
  rowindex<=rows1,
  INDEX(r.1,rowindex,colindex),
  INDEX(r.2,rowindex-rows1,colindex))
)

Line breaks added for readability. Note the first five lines of the formula simply declare variables and assign values. The next four lines do the work of combining the ranges using variables created above. This drastically reduces the amount of redundant code in the formula.

The above code shows a direct translation of the original formula based on named ranges already defined. However, named ranges are not required. The version below adapts the formula to accept two ranges as the first two variables, now renamed range1 and range2:

=LET(range1,E5:F9,range2,H5:I10,
rows1,ROWS(range1),
rows2,ROWS(range2),
cols1,COLUMNS(range1),
rowindex,SEQUENCE(rows1+rows2),
colindex,SEQUENCE(1,cols1),
IF(
  rowindex<=rows1,
  INDEX(range1,rowindex,colindex),
  INDEX(range2,rowindex-rows1,colindex))
)

In this version, the LET function still eliminates code redundancy, but named ranges are not required. Instead the user must enter range1 and range2 manually, as the second and forth arguments, respectively. 

Custom function with LAMBDA

The LAMBDA function can be used to create custom functions. The formula on this page is a good candidate, because it is relatively complex. When converted to a custom LAMBDA function, it is much easier to call:

=AppendRange(range1,range2)

See this article for more detail.

Dynamic Array Formulas are available in Excel 365 only.
Author 
Dave Bruns

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.

Download 100+ Important Excel Functions

Get over 100 Excel Functions you should know in one handy PDF.