ARRAY_CONSTRAIN Function in Google Sheets

Hello everyone, I hope you all are doing well. Today, we have brought you a simple, but very useful Google Sheets Function. We are going to learn how to use the Array Constrain Function. This function, as the name suggests, constrains any existing array into the desired size, many times, we have an array with so many rows and columns, but now we need to show a specific row/column, or some rows/columns from it, here we can use the Array Constrain function with ease.

The Array Constrain function comes from the ARRAY function group, so it deals with Arrays by default, we dont need the generic Array Formula to deal with arrays. With that said, let’s move ahead, and I will show you the simple syntax of this function, and some basic and advanced use cases of it with other functions.

Pros of the ARRAY_CONSTRAIN Function in Google Sheets

There are so many Array related functions in Google Sheets, the Array Constrain is a unique function itself, there is no direct function that can trim an array from rows and columns, so whenever you need to access an array and need a sub-array out of it, then you can easily use the Array Constrain Function.

It has a simple syntax and can be easily used with many other functions to solve many complex problems. Below is the simple Syntax of the Array Constrain Function.

Syntax of the ARRAY_CONSTRAIN Function

ARRAY_CONSTRAIN(input_range, num_rows, num_cols)

Here,

input_range: The cell range of an Array to be constrained.

num_rows: The number of rows the result should contain, such as 2.

num_cols: The number of columns the result should contain, such as 4.

Note: All three arguments are mandatory.

Step-by-Step Procedure to Learn the ARRAY_CONSTRAIN Function in Google Sheets

After learning the fundamentals, and the syntax of the Array Constrain function, now we need to learn it using some use cases, we will show you some simple usage, and some complex usage of this function, and we will also use it with other functions. So, let’s get started with the practical learning.

Simple Usage of the ARRAY_CONSTRAIN Function

In this first section, we will show you the simplest way you can use the Array Constrain function in Google Sheets.

So, let’s suppose you have a simple dataset in rows/columns, and now you need to recall this array but only want to show specific rows or columns.

Step 1

Open Google Sheets, and have some sample data in row columns.

Note: I have generated the random data using the RandArray Function.

Step 2

Now, on any empty cell, where you want the result, start with the =Array_Constrain Function.

Step 3

As the first argument, you need to pass the entire existing data range, here, we have A1:E5.

Step 4

Add a comma, and in the second argument, specify how many rows you need in return, such as 2, so it will return the top 2 rows only.

Step 5

Add a comma, and now for the last argument, specify a number for the columns you need in the result, such as 4, so it will return the 4 columns starting from the left side.

Step 6

The syntax is complete, now press the Enter key, and you will get a new array from your existing array with the specified number of rows and columns.

This was the simplest usage of the Array Constrain Function in Google Sheets.

ARRAY_CONSTRAIN Function with Filter Function

In this section, we are learning a complex example. So, let’s say we have the exam results of 50 students, with all relevant information like name, enrollment number, obtained marks, grade, etc. You need to constrain the large array of the results and get a sub-array, not based on random rows and columns, but based on some filters, we can display only the students having at least a B+ Grade, or the marks must be more than 80%.

We will simply use the Array Constrain function with the Filter function to constrain the existing array using some filters.

Step 1

I have sample data of 50 students, with their results and other relevant results.

Now, I want to retrieve only a few rows and columns, and I have a condition as well.

Step 2

For specifying the condition, I will use the Filter function within the Array Constrain Function.

Step 3

For the first argument of the Filter function, I will provide the entire range.

Step 4

To compare the data with any column, you need to specify its range, here I am comparing with column E where I have the obtained marks of the students.

Step 5

I will add a logical operator such as less than, less than equal to, equal to, not equal to, greater than, or greater than equal to.

I am using greater than in my logic, so I will use a > mark with a numeric value.

Step 6

Close the parenthesis for the Filter function.

Step 7

Add the required number of rows and columns separately to get the resultant array.

Step 8

Press the Enter key, and you will get the result as required.

Final Formula: =ARRAY_CONSTRAIN(FILTER(A1:F51, E1:E51 > 90), 6, 5)

So, this is how you can add up more functions inside the Array Constrain function to solve even more complex problems.

Now, you might be thinking that, when we pass the row and column count, we get them with the fixed starting, like if I need 5 rows I will get rows 1-5, and if I need 3 columns, I will get 1-3 columns, the starting point is fixed, but what if you need to define starting and ending points?

So, now we will use another function within the Array Constrain Function to tackle this problem.

ARRAY_CONSTRAIN Function with Offset Function

The limitation of the Array Constrain is that it does not allow the definition of the starting point of the row or column, it only asks for columns or rows, but not their starting points, so to solve this problem, we will add up the Offset function with the Array Constrain function, and we will be able to define starting and ending points both to retrieve rows and columns as per our needs.

Step 1

We can combine the Array Constrain with Offset function, and use them under an ArrayFormula.

Step 2

We will define the parameters of the Offset Function, and these parameters will define from which row/column to which row/column you want to retrieve the data after constraining the original array.

Step 3

Execute the custom formula, and you will get the results.

Final Formula: =ARRAYFORMULA({A10:F10; ARRAY_CONSTRAIN(OFFSET(A11, 0, 0, 6, 6), 6, 6)})

Similarly, based on the scenario, you can use the Array Constrain function with many other functions to simplify your complex problems.

ARRAY_CONSTRAIN Function with Query Function

=ARRAY_CONSTRAIN(QUERY(A1:D100, “SELECT A, B WHERE B > 5”), 5, 2)

ARRAY_CONSTRAIN Function with Index Function

=ARRAY_CONSTRAIN(INDEX(D1:D5, 1, 1):D5, 3, 1)

ARRAY_CONSTRAIN Function with Sort Function

=ARRAY_CONSTRAIN(SORT(A1:E5, 2, FALSE), 3, 3)

ARRAY_CONSTRAIN Function with Unique Function

=ARRAY_CONSTRAIN(UNIQUE(A1:E5), 4, 3)

FAQs

Where ARRAY_CONSTRAIN Function can be better than other functions?

ARRAY_CONSTRAIN can play significantly better than other functions in some cases, it is useful when you need to extract a specific subset of rows and columns from a larger array or range. Unlike other functions, FILTER or QUERY, ARRAY_CONSTRAIN allows you to specify both the number of rows and columns in the output.

What are the limitations of ARRAY_CONSTRAIN?

ARRAY_CONSTRAIN is a static function, it means that the size of the output array is always fixed and can not be changed dynamically on run time, or based on the scenario. While Query and Filter functions can easily do that.

Get the Practice Worksheet

ARRAY_CONSTRAIN Function in Google Sheets

Conclusion

Thats all about how to use the Array_Constrain Function in Google Sheets. I hope, that by learning and implementing the above examples a beginner and even a mid-level user can improve their understanding of Arrays, and the Array Constrain Function. I will see you soon with another useful guide. Thanks for reading Office Demy tutorials.