Explanation
Note: this example was created before theVSTACK functionandHSTACK functionwere introduced to Excel.
VSTACK combines ranges vertically and HSTACK combines ranges horizontally.
you’re free to use theLAMBDA functionto create a custom function to combine ranges.
Holding the whole thing together areLAMBDAandLET:
This formula is based on asimplified formula explained here.
It’s a good example of how the LAMBDA function and LET function work well together.
Inside the LET function, the first six lines of code simply assign values to variables.
Once values are assigned, these variables drive the output of the function.
Ifrowindexis less than or equal to the count of the rows inrange1(5),INDEXfetches rows fromrange1.
If therowindexis greater than 5, INDEX fetches rows fromrange2.
This is done as a way to catch errors that occur when ranges of different column counts are combined.
In this case, the default value will be output instead of an error.
LET Function
The Excel LET function lets you define named variables in a formula.
you might use INDEX to retrieve individual values, or entire rows and columns.
The array can be one dimensional, or two-dimensional, determined byrowsandcolumnsarguments.
