Help with Google Sheets QUERY function for filtering data

[alexr1990]

I’m building a raffle tracking system for my online gaming group using Google Forms and Sheets. My main sheet collects form data and marks entries that need review by moderators. Each entry can have up to 10 raffle numbers in separate columns.

I want to create a search page with 3 input fields: start date, end date, and raffle number. When someone enters these values, I need a query that finds all entries where the submission date falls between the date range AND any of the 10 raffle number columns matches the searched number.

Here’s my current attempt but it’s not working:

=QUERY("Responses!A2:P1004", "select C where Responses!A > date A:2 AND Responses!A < date B:2 AND (Responses!F = C:2 OR Responses!G = C:2 OR Responses!H = C:2 OR Responses!I = C:2 OR Responses!J = C:2 OR Responses!K = C:2 OR Responses!L = C:2 OR Responses!M = C:2 OR Responses!N = C:2 OR Responses!O = C:2)")

I’m struggling with the QUERY syntax and not sure if I’m referencing the input cells correctly. Can someone explain how to properly structure this query to check multiple columns against my search criteria?

[AdventurousHiker17]

your query syntax is wrong - you can’t reference cells like A:2, it needs to be A2. also, use datetime format for date comparisons. try this: =QUERY(Responses!A2:P1004,"select C where A >= datetime '"&TEXT(A2,"yyyy-mm-dd")&"' and A <= datetime '"&TEXT(B2,"yyyy-mm-dd")&"' and (F="&C2&" or G="&C2&" etc...")

[livbrown]

You’re mixing cell references with column references wrong. I had the same issue tracking inventory across warehouses - building the query string dynamically works way better than cramming everything together directly. Try this: =QUERY(Responses!A2:P1004,"select C where A >= date '"&TEXT(A2,"yyyy-mm-dd")&"' and A <= date '"&TEXT(B2,"yyyy-mm-dd")&"' and (F='"&C2&"' or G='"&C2&"' or H='"&C2&"' or I='"&C2&"' or J='"&C2&"' or K='"&C2&"' or L='"&C2&"' or M='"&C2&"' or N='"&C2&"' or O='"&C2&"')"). See how the cell references sit outside the query string and get concatenated properly? Also, wrap your raffle numbers in single quotes if they have letters or leading zeros - treats them as text.

[alexlee]

Had the same problem building a tournament tracker last year. Your date comparison format is the issue - Google Sheets QUERY is super picky about dates. Don’t use date A:2, format it like date '2024-01-15' in the query string instead. Your OR conditions for multiple columns look good, just fix the cell reference concatenation. Try this: =QUERY(Responses!A2:P1004,"select C where A >= date '"&TEXT(A2,"yyyy-mm-dd")&"' and A <= date '"&TEXT(B2,"yyyy-mm-dd")&"' and (F='"&C2&"' or G='"&C2&"' or H='"&C2&"' etc...)"). Getting those quote marks and concatenation operators right is key so the query engine can actually parse your dynamic values.