Is there an equivalent, in Google Sheets, of Excel's Dynamic Array # operator?

user_4913575 0
Viewed 660

I find one of the most useful aspects of Excel's new(-ish) Dynamic Arrays is that the # operator lets me refer to a range of cells where the number of rows is unknown (and liable to change). Is there anything equivalent in Google Sheets?

For example (view here; download here). Suppose I have a simple two-column array of raw data, containing names in the first column and ages in the second. I then create a new array using FILTER(), where I let the user of my spreadsheet choose an age by which to filter the raw data. I then do some further operations on that FILTER'ed array.

Because I cannot know in advance which ages the user will choose as filtering criteria, I cannot know in advance how many rows the resulting FILTER'ed array will have, which could be a problem. However, with Excel, I don't need to know because I can use the # operator to access the FILTER'ed array without knowing the size. Or I can use ROWS(G2#) -- assuming that's the array top left cell -- to get the number if I want it.

Does Google Sheets have anything equivalent?

To be clear: I can come up with all manner of ways of achieving, in Google Sheets, pretty much the effect I get in Excel. But they're cumbersome and time consuming. What I'm looking for is something as simple and easy as Excel's # operator that I may simply have overlooked.

Is there?

google-query-language google-sheets filter excel arrays
1 Answers

in google sheets you do:

G2:G

or if you want it limited then:

G2:G100

this can be used in formula as a range. tho if you want to use it standalone you do

={G2:G}

or:

=INDEX(G2:G)

or:

=ARRAYFORMULA(G2:G)

in your scenario you would use:

=FILTER(A:A; B:B>=18)

or you can use sql query like:

=QUERY(A:B; "select A where B >= 18")
user_56326290
Related