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.

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)

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.

Performance-related

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))

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.

Functionality-related

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))

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))

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)))

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.