Adding onto an array in Excel/Google Sheets

Question

Let's say I have a 2x2 array in Excel/Google Sheets:

={1,2;3,4}

To add on additional rows to this array -- for example, to make it 4x2, I can use ;:

However, how could additional columns be added on, for example, to make the array 2x4, something like:

@JvdV how could that be done in Excel? Or it's not possible to combine a range with an array literal? — David542
– David542, Commented Dec 28, 2020 at 9:06
It won't be pretty, but if you want to output it like you have, one way is =CHOOSE({1;2;3;4},B5:B6,C5:C6,{5,7},{6,8}) — JvdV
– JvdV, Commented Dec 28, 2020 at 9:13
@JvdV -- neat. How does {1;2;3;4} work as the index_num though? — David542
– David542, Commented Dec 28, 2020 at 9:19
Because I assumed you have access to dynamic-arrays using Excel 365, we can feed CHOOSE() an array. In this case a horizontal array, and it will return us all 4 parameters behind it which are vertical arrays. I hope that is clear =). But I think you can just stick to GoogleSheets if you want a user-friendly approach like you have =) — JvdV
– JvdV, Commented Dec 28, 2020 at 9:23

David542 · Accepted Answer · 2020-12-28 09:04:29Z

3

Arrays allow nesting in Google Sheets so you can try something like:

={B5:C6, {5,6;7,8}}

answered Dec 28, 2020 at 9:04

David542

112k211 gold badges581 silver badges1.1k bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

A very valuable answer.

marikamitsos · Accepted Answer · 2020-12-28 13:26:09Z

1

In Google Sheets you can use the following 2 formulas

=SEQUENCE(4,2)

={SEQUENCE(2,2),SEQUENCE(2,2,5)}

Functions used:

answered Dec 28, 2020 at 13:26

marikamitsos

10.6k23 silver badges29 bronze badges