ARRAY_CONSTRAIN returns the top-left portion of a range or array. Set the maximum rows and columns to show a smaller result without changing the source data.
ARRAY_CONSTRAIN function syntax
=ARRAY_CONSTRAIN(input_range, num_rows, num_cols)- input_range: the source range or the array returned by another formula.
- num_rows: the maximum number of output rows. Use a positive whole number.
- num_cols: the maximum number of output columns. The function keeps columns from the left.
Set up the example data
Enter this dataset starting in A1. The first row contains headings. Keep the result area separate from the source table.
| Name | Sales | Bonus |
|---|---|---|
| Maya | 250 | 25 |
| Leo | 180 | 18 |
| Ana | 320 | 32 |
| Omar | 290 | 29 |
Return the first three values
Enter this formula in A8. It takes the first three names from A2:A5 and returns Maya, Leo and Ana. The fourth name remains in the source but is outside the result.
=ARRAY_CONSTRAIN(A2:A5,3,1)Result: Maya; Leo; Ana.
Crop rows and columns together
Use the same source table to return three rows and only two columns. The Bonus column is omitted; the output contains names and sales.
=ARRAY_CONSTRAIN(A2:C5,3,2)Result: Maya, 250; Leo, 180; Ana, 320.
Keep the top three sales with SORT
SORT first orders the name-and-sales range by its second column, descending. ARRAY_CONSTRAIN then keeps the first three rows of the sorted result.
=ARRAY_CONSTRAIN(SORT(A2:B5,2,FALSE),3,2)Result: Ana, 320; Omar, 290; Maya, 250.
Limit FILTER to the first two matches
FILTER keeps rows whose sales exceed 200. ARRAY_CONSTRAIN limits that result to the first two qualifying rows in source order. These are Maya and Ana, rather than the two highest sales.
=ARRAY_CONSTRAIN(FILTER(A2:B5,B2:B5>200),2,2)Result: Maya, 250; Ana, 320.
Crop a generated SEQUENCE grid
SEQUENCE creates five rows and three columns, starting at 1. Keeping three rows and two columns returns 1,2 on the first row, 4,5 on the second and 7,8 on the third.
=ARRAY_CONSTRAIN(SEQUENCE(5,3),3,2)Result: 1, 2; 4, 5; 7, 8.
Understand oversized requests and headers
This asks for ten rows and four columns from a four-row, three-column source. The result contains only the available data; ARRAY_CONSTRAIN does not add padding rows or columns.
=ARRAY_CONSTRAIN(A2:C5,10,4)Result: Maya, 250, 25; Leo, 180, 18; Ana, 320, 32; Omar, 290, 29.
Other Google Sheets articles you may also like