Explanation
In this example, the goal is to combine ranges.
With the introduction of the VSTACK function and the HSTACK function, this is quite a simple task.
For details on how these functions work see our documentation here:VSTACK function,HSTACK function.
The original formulas are below for reference.
They are still useful for understanding how you’ve got the option to manipulate arrays in a formula.
The first two lines inside let assignrange1to the variable “a” and assignrange2to the variable “b”.
In the next line, the IF function is used to iterate through the array.
Note: a reader mentioned this formula to me based on thestackoverflow answer here.
Multiple column ranges
The formula to combine ranges with multiple columns is more complex.
Note thatline breakshave been added for readability.
This is a shortcut to keep things simple.
It simply assumes the column counts are the same and requests both columns.
Custom function with LAMBDA
TheLAMBDA functioncan 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:
Seethis articlefor more detail.
you could use INDEX to retrieve individual values, or entire rows and columns.
The array can be one dimensional, or two-dimensional, determined byrowsandcolumnsarguments.
…
ROWS Function
The Excel ROWS function returns the count of rows in a given reference.
COLUMNS Function
The Excel COLUMNS function returns the count of columns in a given reference.
LET Function
The Excel LET function lets you define named variables in a formula.
