What is the Use of ARRAY_CONSTRAIN Formula in Google Sheets

0
69

Do you know what is the use of ARRAY_CONSTRAIN formula in Google Sheets? Probably not. Because it’s not that much popular. But it’s definitely a handy function at certain occasions. Also please do not confuse ARRAY_CONSTRAIN Formula with Google Sheets ARRAYFORMULA. Both are entirely different and the latter is much more powerful than the former. First let us learn the use of ARRAY_CONSTRAIN Formula in Google Sheets.

Before going to few examples, here is the syntax.

ARRAY_CONSTRAIN Function Syntax

ARRAY_CONSTRAIN(input_range, num_rows, num_cols)

input_range is the range to constrain. For example A1:C20.

num_rows is the number of rows that the formula should contain. For example in the above input_range A1:C20 there are twenty rows.

num_cols is the number of columns the formula should contain. Here in the above input_range, the number of columns are three.

See the below image that contain an ARRAY_CONSTRAIN formula and it’s result. I’ve also marked all the parameters in the formula here.

Formula Part Explanation:

=ARRAY_CONSTRAIN(A1:H12,5,4)

I’ve applied the formula in Cell A14. You can see the same on the image. A1:H12 is the entire data range or input_range here. The number of rows to return as per the formula are 5 and number of columns are 4. Hope you could understand what is the use of this ARRAY_CONSTRAIN Formula in Google Sheets.

Learn What is the Use of ARRAY_CONSTRAIN Formula with Practical Examples

Below you can find two examples to the practical use of Array Constrain Function in Google Sheets.

1. ARRAY_CONSTRAIN Formula with QUERY

The following Query formula will return the rows that contain age_group 5-10 in column B.

=query(sourcemaster,”select A,B,C,D,E,F where B=’5-10′”)

Here we have no control over the number of rows return. Here you can use array constrain as below.

=array_constrain(query(sourcemaster,”select A,B,C,D,E,F where B=’5-10′”),2,6)

This formula will return only two rows. In this same way you can control a filter output to certain number of rows.

2. ARRAY_CONSTRAIN Formula with Filter Function

Here is yet another use of Google Sheets Array_Constrain Function.

Normal Filter Formula:

=filter(MasterFile!A2:F16,MasterFile!B2:B16=”5-10″)

Below I’ve limited the output of Filter Formula with Array_Constrain.

=array_constrain(filter(MasterFile!A2:F16,MasterFile!B2:B16=”5-10″),2,6)

Conclusion

Google Sheets Array_Constrain can be used in combination with other functions that return an array result. So you can limit the output to certain number of rows or columns. Hope you understood what is the use of ARRAY_CONSTRAIN formula in Google Sheets.

SHARE

A technology enthusiast and addictive blogger who likes to travel and wish to mingle with different community of people from around the world.