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:
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.
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:
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:
In a similar way, SEQUENCE is also used to create a "column index":
COLUMNS(r.1) // returns 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:
When a row index value is greater than 5, INDEX is used to fetch rows from range 2:
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:
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:
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:
See this article for more detail.