How To Create Arrays In Google Sheets (a.k.a. Array Literals)

Arrays in Google Sheets are collections of data, consisting of rows and columns. You can use arrays in formulas in the same way that you use regular A1-type ranges.

You construct arrays in Google Sheets with curly brackets: { }

They’re also known as ARRAY LITERALS.

How To Create Arrays In Google Sheets

Row Array

Let’s start with a very simple row array example:

Row Array In Google Sheets

The formula to create this array, in A1, is:

= { 1 , 2 , 3 }

The opening and closing curly brackets denote the array.

Commas separate the data into columns.

(Note, if you’re a European user, you use a backslash as the column separator. Read more about syntax differences in your formulas due to your Google Sheets location.)

Column Array

Now consider this column array:

Column Array In Google Sheets

where the formula is:

= { 1 ; 2 ; 3 }

Here, semi-colons create new rows in the array.

Two Dimensional Array

Next, consider this example:

Arrays In Google Sheets

This formula, entered into cell A1, creates a 3 by 2 array that puts data in the range A1:B3:

= { "A" , 1 ; "B" , 2 ; "C" , 3 }

You can see the combination of curly brackets to create the array, commas to create columns, and semi-colons to create new rows.

Array Literal Notes

You’re not limited to numbers or strings inside the array literals. You can also nest formulas inside the curly brackets, even array formulas. And when you combine formulas inside array literals, you unleash their full power.

Moreover, you can use the outputs of arrays as inputs to other formulas. See below for examples.

Read about arrays in the Google documentation.

Note, there are also other ways to create array outputs, for example, the SEQUENCE function can generate row, column, or 2-d ranges of data.

Examples Of Arrays In Google Sheets

Now that you’ve seen how to create arrays, let’s look at how to use them for practical applications in formulas.

Return Multiple VLOOKUP Columns

With a regular VLOOKUP formula, you’re restricted to returning a single column.

However, if you nest an array literal as the column index, you can return multiple columns at once.

Have a look at this example:

Return multiple values with single vlookup formula in Google Sheets

Notice the array literal nested inside the VLOOKUP, shown in red:

=ArrayFormula(VLOOKUP($A$14,$A$1:$G$9,{2,3,6,7},FALSE))

This VLOOKUP will return results from columns 2, 3, 6, and 7 simultaneously.

Read more about using Vlookup To Return Multiple Columns In Google Sheets.

Combining Formulas in Arrays in Google Sheets

One of the differences between the powerful QUERY function and a Pivot Table, is that the QUERY function doesn’t add a total row by default.

But you can use these arrays in Google Sheets to easily add a dynamic total row.

Essentially, you combine the results of the QUERY function with a SUM function:

= { QUERY ; "TOTAL" , SUM(range) }

which looks like this in your Google Sheet:

Arrays in Google Sheets QUERY table

Read more on how to add a total row to a Query Function table in Google Sheets.

Default values for cells in your Google Sheets

You can also use arrays in Google Sheets to create default values for cells in Google Sheets.

The array literals create a formula that spills into the adjacent cell. This property is used to create a default value.

Let’s see an example.

In a blank Sheet, write the value “Input” in cell A1. In cell B1, type this formula:

={"",100}

Your Sheet will look like this:

Default Value Setup Google Sheet

Try typing 200 in cell C1, over the top of the 100.

Cell C1 will show the 200, but cell B1 now displays a #REF! error.

Now, delete the value you just typed in cell C1. The error message disappears and the default value of 100 is displayed again.

Default cell Google Sheets

Finally, hide column B so that the #REF! error is never seen, and you have a default value of 100 set for cell C1.

Hidden column default value Google Sheets

Read more about default values in Google Sheets, including a more advanced method to create default values without requiring a hidden column.

5 thoughts on “How To Create Arrays In Google Sheets (a.k.a. Array Literals)”

  1. The array laterals within Vlookups are super helpful so thank you for sharing. They work really well for user defined tables in Dashboards, especially so if you add an additional Lookup within the array.

    Eg:
    =ArrayFormula(iferror(vlookup(A1:A10,Data,{vlookup(B1:B10,DateLookUp,2,0)},0),””))

    The embedded lookup brings up an array of results from a look up table (in this case dates) to bring back certain column refs. This could also be combined with INDEX/MATCH to bring back the column results the user would like. For example in an organisation the user could select employee age, experience and salary. Then it will look for those columns and bring back the specified from the data behind the scenes. It would array vertically across all the employees, which in turn could be a dynamic list based on other criteria such as team, or department.

    Super powerful function so cheers for highlighting it for us!

    1. I’m having a hard time visualizing how this works, Dave. Would you be willing to share a copy of your spreadsheet, or a short video screencast showing how this works?

  2. REF : https://www.benlcollins.com/spreadsheets/arrays-in-google-sheets/

    Thanks for the great explanation about arrays.
    But is there maybe a way to call these created array from a string.
    The idea is to let user select the required data on personal base to create it a personalised dataset.

    When I copy ” { studeertijden!A:A \ studeertijden!F:F \ studeertijden!G:G } ” and add “=” , it works fine.

    SCREENSHOT : https://i.postimg.cc/tJn6qyqN/Schermafbeelding-2022-10-29-om-06-46-27.png
    remark : europe – \ [backslash] instead of , [comma]

  3. thanks for the tutorial. I have question. I use array to array more than one query with condition (where, etc). But when one query is blank, the array turn to #VALUE!. I have adding iferror function in every query, but it not help. Do you have any solution for my case?

Leave a Reply

Your email address will not be published. Required fields are marked *