If you are regularly working with arrays in Google Sheets, there are a number of built-in ARRAY functions that are useful to know about.
One such function is the ARRAY_CONSTRAIN function.
In this tutorial, I will show you how to use the ARRAY_CONSTRAIN Function in Google Sheets
Table of Contents
ARRAY_CONSTRAIN Syntax
The ARRAY_CONSTRAIN function will return an array according to the specified number of rows and columns. It constrains the array to a specific size. This is useful if you only want to return a portion of the data.
The syntax of the ARRAY_CONSTRAIN function is:
=ARRAY_CONSTRAIN(input_range, num_rows, num_cols)
- input_range – this is the original data range that you want to return only a specific number of rows and columns from
- num_rows – this is the number of rows to return
- num_cols – this is the number of columns to return
Using ARRAY_CONSTRAIN
Using ARRAY_CONSTRAIN is simple. Select the cell where you want the constrained version of your input range to return and then put in the input range, the rows, and the columns that you want returned:
The formula I used in this example is =ARRAY_CONSTRAIN(A1:E9,5,5).
This will return 5 rows and 5 columsn from my input range of A1:E9
Closing Thoughts
ARRAY_CONSTRAIN is a quick way to return a specific range of data. It is most often used in combination with other functions that returns arrays when you only want to work with a specific portion of the data.
More Google Sheets Tutorials:
How to Use ARRAYFORMULA
How to Use the MMULT Function