Google Sheets Array Formula Example and Usage: ARRAYFORMULA Function

17

The ARRAYFORMULA function is the dedicated function for creating array formulas in Google Sheets.

Unlike Excel, Google Sheets does not have a built-in way to dynamically spill an array formula result down or across. You can use the ARRAYFORMULA function to achieve a similar effect, but you must wrap the formula in question with the function.

The INDEX function can also be used to create array formulas, but it is not as efficient as the ARRAYFORMULA function in Google Sheets. Therefore, I recommend sticking with the ARRAYFORMULA function unless you have a specific reason to use the INDEX function.

ARRAYFORMULA Function: Syntax and Arguments

Syntax of the ARRAYFORMULA Function in Google Sheets:

ARRAYFORMULA(array_formula)

The function has only one argument and it’s array_formula.

Notes:

  1. The array_formula argument can be a range of cells. For example, =ARRAYFORMULA(A2:A10) will return the values in cells A2 to A10.
  2. The array_formula argument can be a mathematical expression. For example, =ARRAYFORMULA(B2:B10*25) will return the values in cells B2 to B10 multiplied by 25.
  3. The array_formula argument can be a function that returns a result greater than a single cell. For example, =ARRAYFORMULA(FILTER(Sheet1!B2:B10,Sheet1!B2:B10>5)*25) will return the values in cells B2 to B10 that are greater than 5 multiplied by 25.
Argument of the ARRAYFORMULA Function Explained
Figure 1

Array Formula Examples in Google Sheets

In the following example, to calculate the amount of each product, I have used the following array formula in cell E3.

=ARRAYFORMULA(C3:C7*D3:D7)
Array Formula Basic Examples in Google Sheets
Figure 2

You can enter the ARRAYFORMULA function in Google Sheets in two ways:

  1. Manually: Type =ARRAYFORMULA(, then type the formula to expand inside it and press the enter key.
  2. Automatically: In this, you need to type the formula (should start with the = sign) and press Ctrl+Shift+Enter (Windows) or ⌘ + Shift + Enter (Mac).

In the above example, if you do not use the ARRAYFORMULA function and adopt a non-array formula, you need to use =C3*D3 in cell E3 and fill down the formula to the rows below.

To fill it down, you can use either of the following approaches:

  1. Copy the E3 formula and paste it in E4:E7. To do this, press Ctrl+C (Windows) or ⌘+C (Mac) in cell E3, then select cells E4:E7 and press Ctrl+V (Windows) or ⌘+V (Mac).
  2. Select cells E3:E7 and press Ctrl+Enter (Windows) or ⌘+Enter (Mac) to auto-fill the formula.
  3. Go to cell E3 and drag the fill handle (a small square in the bottom-right corner of the selected cell) down.

Here are a few more basic ARRAYFORMULA function examples in Google Sheets.

  1. =ARRAYFORMULA(LEFT(A2:A10,2)): Returns the first two characters from the texts in A2:A10.
  2. =ARRAYFORMULA(TO_DATE(IFERROR(EOMONTH(DATEVALUE(B1:B1000),0)))): Convert the dates in B1:B1000 to month-end dates.
  3. =ARRAYFORMULA(A1:A100&" "&B1:B100): Combine first and last names in the cell range A1:B100.

5 Things to Keep in Mind When Using Array Formulas in Google Sheets

You should know when to use the ARRAYFORMULA function in Google Sheets to return an array result. The following 5 key points will help you understand how to use it and also troubleshoot any errors that may occur.

1. Make sure that the array formula has enough blank cells to expand to. If the array formula does not have enough blank cells, it will return a #REF! error.

2. When using open ranges, place the formula in the first row (in vertical data) or column (in horizontal data) of the range. This will ensure that the formula expands to the correct number of cells.

The following array formula in cell C3 in Google Sheets returns the error “Resut was not expanded automatically, please insert more rows(1)”.

=ARRAYFORMULA(ROW(A2:A))
Figure 3

Do not enter this formula in row#3 or any row below. Doing so will cause the formula to add several blank rows at the bottom of the sheet and also affect its performance.

3. Not all uses of the ARRAYFORMULA function are meant to return array results. In some cases, the ARRAYFORMULA function can be used to expand the result of another function, such as the YEAR function in the following example:

=ARRAYFORMULA(COUNTIF(YEAR(B1:B),2023))

This COUNTIF formula returns the number of cells in the date range B1:B that contain the year 2023. The ARRAYFORMULA function is used to expand the result of the YEAR function.

Here is one more example. In this case, the ARRAYFORMULA function is used to expand C2:C5>5, not the AND logical function.

=ARRAYFORMULA(AND(C2:C5>5))

4. Be aware of the functions and expressions that are not supported by the ARRAYFORMULA function in Google Sheets.

How do I find them?

Here is a rule of thumb that I follow to find the functions that do not support the ARRAYFORMULA function in Google Sheets:

If a non-criteria-based function accepts a range argument and returns a single result, then the array formula may not be able to expand it. For example, SUM, MAX, ISDATE, AND, OR, etc.

Here is an example using the logical AND function where cell range C2:C6 contains Boolean TRUE or FALSE values.

=AND(C2:C6)

This formula will return TRUE if all the Boolean values in the range are TRUE. Otherwise, it will return FALSE.

If a criteria-based function accepts a range argument and multiple criteria from a range, then it can expand. Examples are COUNTIF, SUMIF, VLOOKUP, XLOOKUP, IF, IFS, etc.

However, if you use multiple criteria from multiple ranges, some functions will return an array result, while others will not. For example, the SUMIFS function will not return an array result, but the COUNTIFS function will.

5. Some functions can automatically expand to neighboring cells without the use of the ARRAYFORMULA function. These functions include SEQUENCE, INDEX, QUERY, FILTER, SORT, IMPORTRANGE, VSTACK, HSTACK, etc.

Must Check: Google Sheets Function Guide.

Benefits of Using the ARRAYFORMULA Function in Google Sheets

Here are the main benefits of using the ARRAYFORMULA function in Google Sheets

  • Can improve spreadsheet performance: An array formula can replace thousands of non-array formulas, which can improve spreadsheet performance.
  • Make formula corrections easier: If you need to modify a formula, you can do it quickly if you use an array formula. You only need to edit the formula in a single cell.
  • Eliminate errors: Non-array formulas are prone to errors when the sheet layout is modified. Adding rows or columns may cause empty cells in the formula-applied row or column. An array formula can automatically fill them.
  • Data cleaning: It is faster to remove old formulas from your sheet if you have array formulas. You only need to press the delete button in one or two cells, depeding on the number of array formulas.

How to Use the ARRAYFORMULA Function with VLOOKUP, XLOOKUP, COUNTIF, and SUMIF in Google Sheets

Assume we have a list of the top 10 fruits and their native origins in the range A1:B11, where A2:A11 contains fruit names and B2:B11 contains the native origin. A1 and B1 cells are for the titles.

The criteria are the fruit names “Strawberry” and “Mango” in cells D2 and D3, respectively. How to return the native origin using VLOOKUP and XLOOKUP?

Here is the VLOOKUP array formula to search fruit names in the range A2:A11 and return native origin in the range B2:B11.

VLOOKUP Syntax: VLOOKUP(search_key, range, index, [is_sorted])

=ARRAYFORMULA(VLOOKUP(D2:D3,A2:B11,2,FALSE))

Here is the corresponding XLOOKUP array formula in Google Sheets.

XLOOKUP Syntax: XLOOKUP(search_key, lookup_range, result_range, [missing_value], [match_mode], [search_mode])

=ARRAYFORMULA(XLOOKUP(D2:D3,A2:A11,B2:B11))
Using XLOOKUP function with the ARRAYFORMULA function in Google Sheets
Figure 4

You May Like: VLOOKUP and XLOOKUP: Key Differences in Google Sheets.

In the same data range, you want to find the number of instances of “North America” and “Southeast Asia” in the range B2:B11.

If the criteria are in cells D2:D3, the following COUNTIF array formula will return the results 1 and 3.

COUNTIF Syntax: COUNTIF(range, criterion)

=ARRAYFORMULA(COUNTIF(B2:B11,D2:D3))

Here is one more example of how to use the array formula in Google Sheets.

The following ARRAYFORMULA and SUMIF combination returns the sum of the values in column B for the column A values matching the criteria in cell range D4:D6.

=ARRAYFORMULA(SUMIF(A1:A,D4:D6,B1:B))
SUMIF Array Formula (Basic)
Figure 5

What LAMBDA Can Do That ARRAYFORMULA Can’t

This is an additional tip. I’ve already explained that the ARRAYFORMULA function in Google Sheets cannot expand all formulas. For example, we cannot expand the following MIN function using the ARRAYFORMULA function in Google Sheets.

=MIN(B2:E2)

Please see the cell range G2:G5 in the screenshot below for the examples. We can make it expand using BYROW, one of the LAMBDA helper functions.

=BYROW(B2:E5,LAMBDA(v,MIN(v)))
Lambda Formula: BYROW with MIN in Google Sheets
Figure 6

Conclusion

Learning the ARRAYFORMULA function is the first step in becoming proficient in Google Sheets. Array formulas can change the way you use Google Sheets.

Always remember to keep only the rows and columns you want in your sheet to enhance your sheet’s performance. Also, follow my instructions when you use open ranges in this function.

Prashanth KV
Introducing Prashanth KV: Your Trusted Google Sheets and Excel Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.

17 COMMENTS

  1. Hi Prashanth

    I want to use ArraFormula with SpellNumIND in Google Sheets. I have no idea how to use it. Can you help me with it?

    Thanking you,

    Mehul Patel

  2. Hi Prashanth, I am reaching out to see if you can help me understand the ArrayFormula with various functions.

    I don’t want the array formula to count the blank (looking) cells.

    — URL (Sheets’) removed —

    Do you know of any way I can stop those cells from being counted?

    Thank you, Karen.

    • Hi, Karen,

      The following array formula in cell A2 returns a white space in blank rows when columns C and B are empty.

      =ArrayFormula(C2:C&" "&B2:B)

      Replace this formula as below to include a trim().

      =ArrayFormula(TRIM(C2:C&" "&B2:B))

      As a side note, to limit the expansion of your countifs(), use the LEN as below.

      =ArrayFormula(if(len(F2:F),countifs(A2:A,F2:F),))

  3. I want to use Vlookup with array and ifs in this:

    1. =vlookup(A27,{'Raw Materials'!A6:A29,'Raw Materials'!K6:K29},2,false)/100*H27
    2. =(vlookup(A28,{'Raw Materials'!A30:A149,'Raw Materials'!C30:C149},2,false))/100*H28
    3. =vlookup(A29,{'Raw Materials'!A30:A149,'Raw Materials'!C30:C149},2,false)*H29
    4. =vlookup(A30,{'Raw Materials'!A30:A149,'Raw Materials'!C30:C149},2,false)/100*H30

    How can I make them as one?

    • Hi, Fatima,

      You may try this.

      =ArrayFormula(vlookup(A27:A30,{{'Raw Materials'!A30:A149;'Raw Materials'!A6:A29},{'Raw Materials'!C30:C149;'Raw Materials'!K6:K29}},2,false)/100*H27:H30)

  4. In Google Sheets, I have a “Sheet A” and “Sheet B”

    In Sheet A, I have a set of unique text observations (via ArrayFormula) from column A in Sheet B. I want to set up a formula that takes the Sumproduct of all rows in columns C and D in Sheet B where column A text values match the unique text observation in Sheet A.

    Any advice here?

  5. Hi Prashanth,

    I have been trying to collect data through Google Forms to Google Sheets and then sending an acknowledgment using a merging & email addon.

    I tried generating a unique application number in the series 20200001, 20200002, 20200003 ….and so on.

    For which, I tried using the following formula.

    =arrayformula(if(isblank(A3:A),"",AR2:AR+1))

    I had keyed AR1 as “Application Red No” & AR2 as “20200001”.
    I was assuming that I will get a series in ascending order.

    But, the output was as below

    Application Ref No
    20200001
    20200002
    20200003
    4
    2
    2
    2
    2
    2

    Not sure, where I went wrong. Can you assist?

    Kindly Help.

  6. Dear Mr. Prashanth,

    Suppose, cell A1=”Test”

    I want to copy the value of A1, to A2, A3,…. and to the cells of the rest of column A

    How to do so?

    Thanks for any kind of help!

    Timy

  7. Dear Mr. Prashanth

    I am a new follower of your this blog. But according to my knowledge your explain style is very easy for our understanding…Well done and go ahead Prashanth…The reasons are before joint this blog I have never seen and understand this kind of simple explanation.
    Fantastic your work. We will be with you.

    Regards

    Kelum Erandika

  8. Hi Michael,

    I am sorry for that. I am non native English speaker that may cause the issue.
    If you have any specific doubt on any part of this tutorial, please point out here. So that I can go in detail.

    Thanks for your understanding.

  9. I’m having a very difficult time understanding your tutorials. I’m not all that new to google sheets and I couldn’t make heads or tails of the two I have looked at so far.

LEAVE A REPLY

Please enter your comment!
Please enter your name here