How to filter or query an array made using {} in Google Sheets

1
votes

this is the first question I've ever posted on here so I apologize if I did something incorrectly.

I've got a sheet that I used to keep track of the release dates of various products. I want to be able to filter all of these products based on whether or not their release date is the current day. However, I'm having trouble using FILTER or QUERY to do this. After doing some research I've learned how to "stack" the list of products on top of each other as an array to then filter, but I can't quite get it to work. I had it to where it would work if rows in both of the sections I was testing in had a match, but if one didn't then the whole formula returned an error. This was also only using a few rows for each section, where ideally I'd want to look at the entire section.

Here's an example sheet: https://docs.google.com/spreadsheets/d/1sP26Ptuv8eBqLLZoAah49XjrwavcNSMIlyWwG-dBq1A/edit#gid=879002581

And this is the formula I'm using in cell B3: =FILTER({F3:H; J3:L; N3:P; R3:T; V3:X; Z3:AB}, D3:D = 9/20/2019)

But this gives me an error saying it expected a different amount of rows, which makes sense but I don't know how to fix. Specifically, it gives me this error:

FILTER has mismatched range sizes. Expected row count: 654. column count: 1. Actual row count: 109, column count: 1.

I've also tried using QUERY but I don't know how to select what I'm looking for. I tried using both Col1, Col2, etc. and A, B, etc. but it kept giving me errors no matter what I tried.

Between this error and the one I mentioned previously, I'm stuck. Any help would be greatly appreciated.

1
arraysgoogle-sheetsfiltergoogle-sheets-formulagoogle-sheets-query

1 Answers

0
votes

it needs to be like:

=FILTER({F3:H; J3:L; N3:P; R3:T; V3:X; Z3:AB}, 
        {H3:H; L3:L; P3:P; T3:T; X3:X; AB3:AB}=DATE(2019, 9, 20))

then for today you can use:

=FILTER({F3:H; J3:L; N3:P; R3:T; V3:X; Z3:AB}, 
        {H3:H; L3:L; P3:P; T3:T; X3:X; AB3:AB}=TODAY())

and QUERY would be:

=QUERY({F3:H; J3:L; N3:P; R3:T; V3:X; Z3:AB}, 
 "where Col3 = date '"&TEXT(TODAY(), "yyyy-mm-dd")&"'", 0)

0