How to match two cells in different sheets in Google Sheet using QUERY function?

Question

I have a sheet with two tabs:

Tab1: The first tab contains data in three columns named A, B and C

Tab2: The second tab contains data in three columns named A, B and C. It also includes two cells which I'm going to use conditions. I called them OpenTime in cell D2 and CloseTime in cell D3.

Goal: I am trying to use query function to search columns in Tab1 and check if their conditions are true, return the value. These are three conditions: 1. Open Time > (June 30, 2019 5:00 PM) 2. Close Time < (June 30, 2019 6:00 PM) 3. C (Value in column C of Tab1) equals to C2 in Tab2 (Column C row 1 in Tab 2) This is what I wrote:

=query(Tab1!$A:$C,"select $B where $A> timestamp '"& text(D2,"yyyy-MM-dd HH:mm:ss")&"'" & "AND $A < timestamp '"&text(D3,"yyyy-MM-dd HH:mm:ss")&"'" & " AND $C Like '"&C2&"'",0)

If I apply the above query in Tab1, it works, However, when I paste it in the second Tab (Tab2) it returns an error. What am I doing wrong?

Here is the error:

Unable to parse query string for Function QUERY parameter 2: PARSE_ERROR: Encountered " "$ "" at line 1, column 8. Was expecting one of: "true" ... "false" ... "date" ... "timeofday" ... "datetime" ... "timestamp" ... "min" ... "max" ... "avg" ... "count" ... "sum" ... "no_values" ... "no_format" ... "is" ... "null" ... "year" ... "month" ... "day" ... "hour" ... "minute" ... "second" ... "millisecond" ... "with" ... "contains" ... "starts" ... "ends" ... "matches" ... "like" ... "now" ... "dateDiff" ... "quarter" ... "lower" ... "upper" ... "dayOfWeek" ... "toDate" ... ... ... ... ... ... "(" ... "*" ... "-" ...

Update: Link to the spreadsheet:

player0 player0 · Accepted Answer · 2019-07-01T11:23:45

correct syntax should be:

=IF(IFERROR(QUERY(LiveAttendanceForm!$A:$C,
 "select B 
  where A > timestamp '"&TEXT(D$1, "yyyy-MM-dd HH:mm:ss")&"'
    and A < timestamp '"&TEXT(D$2, "yyyy-MM-dd HH:mm:ss")&"'
    and C = "&$C4, 0))=$A4, TRUE)

How to match two cells in different sheets in Google Sheet using QUERY function?

1 Answers