QUERY() would be good choice for this case. Try-
=QUERY('Guest List'!A7:B,"select A where not B matches 'Refund|Cancelled' limit 34")
For second result dataset use-
=QUERY(QUERY('Guest List'!A7:B,"select A where not B matches 'Refund|Cancelled' offset 34"),"limit 34")
So, above formula will drop first 34 records by offset 34 and will display next 24 records by limit 34. And you can continue for more columns just increasing offset multiply by 34 (suppose for 3rd column it will be offset 68).
Reddit
reddit.com › r/excel › eli5, arrayformula and arrayconstrain
r/excel on Reddit: ELI5, ArrayFormula and ArrayConstrain
February 5, 2020 -
I am using these to produce random dice results (Like rolling 3d6 and adding instead of a random number between 1 and 36) and it is producing the correct results but I just dont know why.
Can someone explain how the functions work and what they do in combining data?
Using ARRAYFORMULA or something similar to populate a column that needs to be editable Nov 10, 2020
r/googlesheets 5y ago
Google Support
support.google.com › docs › answer › 3267036
ARRAY_CONSTRAIN - Google Docs Editors Help
Constrains an array result to a specified size. ... Generally used in combination with other functions that return an array result when a fewer number of rows or columns are desired. ARRAYFORMULA: Enables the display of values returned from an array formula into multiple rows and/or columns ...
google sheets - Array_Constrain error even after I remove it - Stack Overflow
I have a sheet with several ARRAYFORMULA(IF....) formulas. I think it was adding 1000s of extra empty rows to my sheet, so I wrapped them in ARRAY_CONSTRAIN(ARRAYFORMULA(IF...)),300,1 For some inst... More on stackoverflow.com
Prevent ARRAY_CONSTRAIN from being added to Google Sheets arrayformula saved file. SOLVED! - Google Docs Editors Community
Skip to main content · Google Docs Editors Help · Sign in · Google Help · Help Center · Community · Google Docs Editors · Terms of Service · Submit feedback · Send feedback on More on support.google.com
Recreating ARRAY_CONSTRAIN(ARRAYFORMULA in excel
I have a formula in a Google Sheet that we are transitioning into excel. I'm not sure if this is the most effective way to accomplish what we'd like to do. We have a chart of dates on one tab (Date Tables), and I'm trying to return the soonest date… More on answers.microsoft.com
5
48
Recreating ARRAY_CONSTRAIN(ARRAYFORMULA in excel
I have a formula in a Google Sheet that we are transitioning into excel. I'm not sure if this is the most effective way to accomplish what we'd like to do. We have a chart of dates on one tab (Date Tables), and I'm trying to return the soonest date… More on learn.microsoft.com
5
48
Why use ARRAY_CONSTRAIN with ARRAYFORMULA instead of just anchoring the range?
An anchored range like `A2:A50` breaks when source data grows or shrinks past 49 rows. `ARRAYFORMULA` inside `ARRAY_CONSTRAIN` keeps the computation dynamic (new data is automatically included in the inner formula) while keeping the output size fixed (the outer constraint ensures exactly N rows land in the model block). This matters when downstream formulas reference fixed row ranges, like a `=SUM(Summary!C2:C9)` in a returns analysis tab.
modelmonkey.io
modelmonkey.io › home › blog › array_constrain + arrayformula in google sheets (2026)
ARRAY_CONSTRAIN + ARRAYFORMULA in Google Sheets (2026) | ModelMonkey
What does ARRAY_CONSTRAIN do in Google Sheets?
`ARRAY_CONSTRAIN(array, num_rows, num_cols)` trims any array or range to exactly the number of rows and columns you specify. It always takes the top-left portion of the array. If the source has fewer rows than `num_rows`, it returns however many rows exist without throwing an error - unlike `INDEX`, which returns `#REF!` in the same situation.
modelmonkey.io
modelmonkey.io › home › blog › array_constrain + arrayformula in google sheets (2026)
ARRAY_CONSTRAIN + ARRAYFORMULA in Google Sheets (2026) | ModelMonkey
What is the Excel equivalent of ARRAY_CONSTRAIN?
`TAKE()`, introduced in Microsoft 365 in 2022. `=TAKE(array, rows, cols)` works similarly but also supports negative row/column values to pull from the end of an array. `ARRAY_CONSTRAIN` doesn't support negative indexing. If you need to port a Sheets model to Excel, replace `ARRAY_CONSTRAIN(x, n, m)` with `TAKE(x, n, m)` - the argument order is identical for positive values.
modelmonkey.io
modelmonkey.io › home › blog › array_constrain + arrayformula in google sheets (2026)
ARRAY_CONSTRAIN + ARRAYFORMULA in Google Sheets (2026) | ModelMonkey
04:23
[GOOGLE SHEETS] ARRAY_CONSTRAIN: RESTRINGIR UN ARREGLO A UN TAMAÑO ...
04:24
New Row, No Work! Array_Constrain() - Automatic Formulas in Google ...
How To Use Array Formula With Array Constraint | Google Sheets
ARRAY_CONSTRAIN Function in Google Sheets
02:16
How to Use the ARRAY_CONSTRAIN Function in Google Sheets - YouTube
14:58
Google Sheets Tutorial Español - Funcion ARRAYFORMULA - Introduccion ...
Stack Overflow
stackoverflow.com › questions › 69970440 › what-does-array-constrain-and-arrayformula-do
excel formula - what does ARRAY_CONSTRAIN and ARRAYFORMULA do? - Stack Overflow
=ARRAY_CONSTRAIN(ARRAYFORMULA(IF(A2="","",INDEX('PLACEMENT List'!$A:$AA,'INVOICE List'!$A2,MATCH('INVOICE List'!$C$1,'PLACEMENT List'!$A$2:$AA$2,0)))), 1, 1)
Stack Overflow
stackoverflow.com › questions › 74937432 › array-constrain-error-even-after-i-remove-it
google sheets - Array_Constrain error even after I remove it - Stack Overflow
=Array_Constrain(ArrayFormula(IF($B$4:$B$250="",, IMPORTRANGE($F$4,"Summary!N4:N250")+IMPORTRANGE($F$5,"Summary!J4:J250"))),300,1)
Google Support
support.google.com › docs › thread › 163893710 › prevent-array-constrain-from-being-added-to-google-sheets-arrayformula-saved-file-solved
Prevent ARRAY_CONSTRAIN from being added to Google Sheets arrayformula saved file. SOLVED! - Google Docs Editors Community
May 16, 2022 - Skip to main content · Google Docs Editors Help · Sign in · Google Help · Help Center · Community · Google Docs Editors · Terms of Service · Submit feedback · Send feedback on
InfoInspired
infoinspired.com › home › google docs › spreadsheet › what is the use of array_constrain formula in google sheets?
What is the Use of ARRAY_CONSTRAIN Formula in Google Sheets?
November 25, 2023 - Let me explain the use of the ARRAY_CONSTRAIN Formula in Google Sheets. The main purpose of the ARRAY_CONSTRAIN function is to limit the result of an array to a specified number of rows and columns.
Microsoft Answers
answers.microsoft.com › en-us › msoffice › forum › all › recreating-arrayconstrainarrayformula-in-excel › c377f35a-21a2-481d-8d51-ca18c9612e1f
Recreating ARRAY_CONSTRAIN(ARRAYFORMULA in excel - Microsoft Q&A
The formula we were using in Google Sheets is =ARRAY_CONSTRAIN(ARRAYFORMULA(IF(E2<>"",MIN(IF('Date Tables'!$B:$B>=(E2+182),'Date Tables'!$B:$B)),"")), 1, 1)
Better Sheets
bettersheets.co › formulas › array-constrain
ARRAY_CONSTRAIN Google Sheets Formula: Constrains an array result to a specified size.
Use the range A1:A10 and constrain it to show only the first 5 rows. Your formula should return an array with 5 rows and 1 column.
ModelMonkey
modelmonkey.io › home › blog › array_constrain + arrayformula in google sheets (2026)
ARRAY_CONSTRAIN + ARRAYFORMULA in Google Sheets (2026) | ModelMonkey
July 11, 2026 - A ARRAYFORMULA computing IRR across 8 leverage scenarios, constrained to 8x1. The scenario labels are hardcoded in column A; the formula block has to match exactly. Rolling period displays. A 13-week cash flow display that always shows exactly 13 columns regardless of how many weeks of actuals exist in the source tab. In each case, ARRAY_CONSTRAIN is doing exactly one job: making a dynamic formula output a predictable size.
Top answer 1 of 2
1
QUERY() would be good choice for this case. Try-
=QUERY('Guest List'!A7:B,"select A where not B matches 'Refund|Cancelled' limit 34")
For second result dataset use-
=QUERY(QUERY('Guest List'!A7:B,"select A where not B matches 'Refund|Cancelled' offset 34"),"limit 34")
So, above formula will drop first 34 records by offset 34 and will display next 24 records by limit 34. And you can continue for more columns just increasing offset multiply by 34 (suppose for 3rd column it will be offset 68).
2 of 2
0
Add a filter for values in the prior columns.
So this would be the final column (K), which filters out the results already added in columns B, E and H:
=ARRAY_CONSTRAIN(arrayformula(filter('Guest List'!A7 :'Guest List'!A140, 'Guest List'!B7:B140 <> "refund", 'Guest List'!B7:B140 <> "cancelled", isna(MATCH('Guest List'!A7 :'Guest List'!A140, B10:B140,0)), isna(MATCH('Guest List'!A7 :'Guest List'!A140, E10:E140,0)), isna(MATCH('Guest List'!A7 :'Guest List'!A140, H10:H140,0)))),34, 1)
Google Support
support.google.com › docs › thread › 290174021 › array-constrain-arrayformula
=ARRAY_CONSTRAIN(ARRAYFORMULA - Google Docs Editors Community
August 10, 2024 - Skip to main content · Google Docs Editors Help · Sign in · Google Help · Help Center · Community · Google Docs Editors · Terms of Service · Submit feedback · Send feedback on
Coefficient
coefficient.io › home
Use Array_Constrain in Google Sheets to limit arrays for focused and efficient spreadsheet management.
September 2, 2025 - In Google Sheets, ARRAY_CONSTRAIN is an essential formula for managing large datasets. It limits an output array to a specific number of rows and columns.
Microsoft Learn
learn.microsoft.com › en-us › answers › questions › 5036829 › issues-with-array-constrain-and-arrayformula
Issues with ARRAY_CONSTRAIN and ARRAYFORMULA - Microsoft Q&A
=ARRAY_CONSTRAIN(ARRAYFORMULA(IF(LEN(D3:D999),IFERROR(VLOOKUP(D3:D999,Alimento!A:F,{2,3,4,5,6},),"???"),)), 997, 5)
Sheetgo
sheetgo.com › home › how to use the array_constrain formula in google sheets
How to use the ARRAY_CONSTRAIN formula in Google Sheets - Sheetgo
April 13, 2017 - In Google Sheets, the ARRAY_CONSTRAIN formula returns a subset of the input range, characterized by the specified number of rows and columns.
Reddit
reddit.com › r/sheets › array_constrain being added to arrayformula
r/sheets on Reddit: Array_constrain being added to Arrayformula
January 15, 2024 -
Hello, I sell a google sheet on Etsy which uses array formula. When people purchase they are sent a link to make a copy of my original sheet. I've had two cases in the last week where customers have come to me saying that the formulas are broken with #NA and in both cases it's because the formula has been wrapped in =ARRAY_CONSTRAIN. I don't think this is happening for everyone. Does anyone have any ideas was to why this is happening?
Appreciate any help, thanks.
Google Support
support.google.com › docs › thread › 176205730 › google-sheets-adds-array-constrain-and-arrayformula-functions-to-my-formulas
Google sheets adds ARRAY_CONSTRAIN and ARRAYFORMULA functions to my formulas - Google Docs Editors Community
August 22, 2022 - Skip to main content · Google Docs Editors Help · Sign in · Google Help · Help Center · Community · Google Docs Editors · Terms of Service · Submit feedback · Send feedback on
Reddit
reddit.com › r/googlesheets › array constrain/formula
r/googlesheets on Reddit: Array Constrain/Formula
January 3, 2026 -
I have a spreadsheet for sports that compares the home team scores against the away team scores. When I use the Array Constrain and ArrayFormula to pull the wins, losses, and ties - it automatically defaults to 0 wins, 0 loss, 3 ties (as the cells are blank). Is there any way to have the ties default to zero if there is no information in the game scores?
Top answer 1 of 3
1
When you use what formula?
2 of 3
1
ARRAY_CONSTRAIN(ARRAYFORMULA(),1,1) is pretty much a dead giveaway of a not-quite-compatible Excel formula automatically translated into Sheets. There’s invariably a better approach but you’ll need to share more information about your file first, both to give people enough information to help you and to remain in compliance with rule 2, which your post in its current form is arguably violating. Like marcnotmark says, you should share the file you are working on or a copy of it.
Google Support
support.google.com › docs › thread › 146672915 › formula-array-constrain-arrayformula-unique-d3-e400-false-false-1-2
Formula =ARRAY_CONSTRAIN(ARRAYFORMULA(UNIQUE(D3:E400,FALSE,FALSE)),1,2) - Google Docs Editors Community
January 19, 2022 - Skip to main content · Google Docs Editors Help · Sign in · Google Help · Help Center · Community · Google Docs Editors · Terms of Service · Submit feedback · Send feedback on