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

Excel cannot directly concatenate arrays in the way you describe (i.e. simply combining them back to back.) However, there is a (complicated) solution to this problem without using helper functions.

Essentially what you need to do is convert {10;11;12;13} to {10;11;12;13;0;0;0} and convert {20;21;22} to {0;0;0;0;20;21;22}. Once you have that result, you can add the two arrays of length 7 together to get the desired result.

So how do you add zeros to the beginning or end of an array?

The answer is to use matrix multiplication (MMULT Excel built-in function) in a clever way.

I won't explain all of the mathematics as to why this is the result because I think it gets too off-topic from programming but ultimately the following matrix multiplication equation gives you the desired result:

[1 0 0 0]   [10]   [10][0 1 0 0] * [11] = [11][0 0 1 0]   [12]   [12][0 0 0 1]   [13]   [13][0 0 0 0]          [ 0][0 0 0 0]          [ 0][0 0 0 0]          [ 0]

Or in Excel, you can type this to get you the result: (I added line breaks for increased readability.)

= MMULT({1,0,0,0;         0,1,0,0;         0,0,1,0;         0,0,0,1;         0,0,0,0;         0,0,0,0;         0,0,0,0},A1:A4)

If you highlight this formula in the cell and press the F9 key, you should notice it will give you the desired result of {10;11;12;13;0;0;0}.

Similarly, the following formula will get you the desired result of {0;0;0;0;20;21;22}:

= MMULT({0,0,0;         0,0,0;         0,0,0;         0,0,0;         1,0,0;         0,1,0;         0,0,1},B1:B3)

Summing these two values together will get the desired final result which is {10;11;12;13;20;21;22}.

NOTE

At this point, this might be enough information for your wants/needs. However, for large arrays, it may be too cumbersome to hard-code these matrices of 1's and 0's into your formula. If this is the case, continue reading which tells you how to generate these matrices of 1's and 0's automatically rather than hard-coding them.

How do we generate these large matrices of 1's and 0's shown above automatically?

Again without explaining much of the "why" because I think the discussion will get too long and off-topic, here is a formula that generates the first matrix of 1's and 0's above:

= (ROW(INDIRECT(ADDRESS(1,1)&":"&ADDRESS(ROWS(A1:A4)+ROWS(B1:B3),1)))  =COLUMN(INDIRECT(ADDRESS(1,1)&":"&ADDRESS(1,ROWS(A1:A4)))))+0

The formula for the 2nd matrix of 1's and 0's is slightly different:

= (ROW(INDIRECT(ADDRESS(1,1)&":"&ADDRESS(ROWS(A1:A4)+ROWS(B1:B3),1)))  =(COLUMN(INDIRECT(ADDRESS(1,1)&":"&ADDRESS(1,ROWS(B1:B3))))+ROWS(A1:A4)))+0

FINAL FORMULA

The final formula to concatenate two (vertical) arrays is the following: (Several line breaks added for increased readability)

= MMULT(    (ROW(INDIRECT(ADDRESS(1,1)&":"&ADDRESS(ROWS(A1:A4)+ROWS(B1:B3),1)))    =COLUMN(INDIRECT(ADDRESS(1,1)&":"&ADDRESS(1,ROWS(A1:A4)))))+0,    A1:A4) +MMULT(    (ROW(INDIRECT(ADDRESS(1,1)&":"&ADDRESS(ROWS(A1:A4)+ROWS(B1:B3),1)))    =(COLUMN(INDIRECT(ADDRESS(1,1)&":"&ADDRESS(1,ROWS(B1:B3))))+ROWS(A1:A4)))+0,    B1:B3)

FINAL NOTES/THOUGHTS

The advantage to using this formula is that it allows arrays to be concatenated without using VBA. The disadvantage is that this method for concatenating arrays only works with numbers, not text. (This is because MMULT requires numbers.)

For what it's worth, here is a solution that concatenates two any two vertical arrays (without the limitation that the data must be numbers).

Here is the array formula: (e.g. combining A1:A4 and C7:C9)

= INDEX(CHOOSE({1,2},A1:A4,C7:C9),  N(IF({1},ROW(INDEX($A:$A,1):INDEX($A:$A,ROWS(A1:A4)+ROWS(C7:C9)))-IF(  ROW(INDEX($A:$A,1):INDEX($A:$A,ROWS(A1:A4)+ROWS(C7:C9)))<=ROWS(A1:A4),0,ROWS(A1:A4)))),  N(IF({1},2-(ROW(INDEX($A:$A,1):INDEX($A:$A,ROWS(A1:A4)+ROWS(C7:C9)))<=ROWS(A1:A4)))))

And here is the array formula to combine two horizontal arrays (e.g. A1:D1 and C3:E3)

= INDEX(CHOOSE({1;2},A1:D1,C3:E3),  N(IF({1},2-(COLUMN(INDEX($1:$1,1):INDEX($1:$1,COLUMNS(A1:D1)+COLUMNS(C3:E3)))  <=COLUMNS(A1:D1)))),N(IF({1},COLUMN(INDEX($1:$1,1):INDEX($1:$1,COLUMNS(A1:D1)+  COLUMNS(C3:E3)))-IF(COLUMN(INDEX($1:$1,1):INDEX($1:$1,COLUMNS(A1:D1)+COLUMNS(C3:E3)))  <=COLUMNS(A1:D1),0,COLUMNS(A1:D1)))))

If you have a dynamic-array version of Excel, Excel 365 or a version after 2019, there's a much shorter answer. If one column is named Foo and the other column is named Bar, your formula would be:

=SMALL((Foo,Bar),SEQUENCE(ROWS(Foo)+ROWS(Bar)))

Here, SEQUENCE returns an array with the sequence from 1 through the total number of rows for Foo and Bar. SMALL returns the appropriate value for each value in the sequence. Then Excel "spills" the results into the necessary number of rows below the formula.