Is there a way to concatenate two arrays in Excel without VBA?

Question

I am trying to create a formula that returns the concatenation of two arrays of different lengths. I need this concatenation for part of another formula and I would like to avoid "helper" rows, if possible.

See below for example data.

The goal is to have the output be {10;11;12;13;20;21;22}. Of course, this can easily be hardcoded into the formula but these values are dynamic so that is not an option.

I tried the following:

{A1:A4;B1:B3}

but this is apparently not valid Excel syntax.

Is there a solution?

Answer 1

If you only need to concatenate two arrays and you have a recent version of Excel, I believe this is the shortest answer which keeps the original order of the arrays.

This answer loops through the s array (which is the combined length of your two goal arrays), and uses an if statement to determine whether to output array a's elements or array b's elements.

To copy and paste this formula in your work book you only need to change the values of A2# & B2# to the two arrays you would like to concatenate.

Multi-Line version

=LET(
    a, A2#,
    b, B2#,
    s, SEQUENCE(ROWS(a) + ROWS(b)),
    IF(s > ROWS(a), 
        INDEX(b, s - ROWS(a)), 
        INDEX(a, s)
    ))

Minified Version:

=LET(a,A2#,b,B2#,s,SEQUENCE(ROWS(a)+ROWS(b)),IF(s>ROWS(a),INDEX(b,s-ROWS(a)),INDEX(a,s)))