I have the following 2 queries, which should give the same result but don't. In the example I am showing it is looking at data from another sheet and finding a match where Col 7 has a date of May 30, 2024. The first I used contains with the date in parentheses and it finds it. But the second, which should look for the date as well, finds no result. What am I missing? (I did go to the other sheet and put =isdate(G6) where G6 is the data for Col7 and it shows true)
=ifna(if(B3="",query(importrange("11EQ9lVbX6ioTECafiXTDxvoWRNDkEAEqj-KdI_-6BBB","'Responses'!AD3:AK")," select * where Col7 contains 'May 30, 2024' ",0),query(importrange("11EQ9lVbX6ioTECafiXTDxvoWRNDkEAEqj-KdI_-6BBB","'Responses'!AD:AL")," select Col1,Col2,Col3,Col4,Col5,Col6,Col7,Col8 where lower(Col9) contains '"&lower(B3)&"' ",0)),"No Results Found")
Finds one result
=ifna(if(B3="",query(importrange("11EQ9lVbX6ioTECafiXTDxvoWRNDkEAEqj-KdI_-6BBB","'Responses'!AD3:AK")," select * where Col7 = date '"&TEXT(DATEVALUE("May 30, 2024"),"yyyy-mm-dd")&"' ",0),query(importrange("11EQ9lVbX6ioTECafiXTDxvoWRNDkEAEqj-KdI_-6BBB","'Responses'!AD:AL")," select Col1,Col2,Col3,Col4,Col5,Col6,Col7,Col8 where lower(Col9) contains '"&lower(B3)&"' ",0)),"No Results Found")
No Result found (should find same result as first one)
The imported spreadsheet doesn't have a sheet named 'Responses'. The 'JGOOT Responses' tab looks like what is meant. In that tab, columns AI:AJ don't contain dates but text strings that just look like dates. The root cause is with the two text() formulas in 'RAW Data Corrected'!V1:W1.
You can convert those two columns into numeric dates like this:
=query(T1:T, "label Col1 'Formatted LEAVE date for email' format Col1 'mmm d, yyyy' ", 1)
You can confirm that the dates are numeric with the isnumber() function. Once they're numeric, you can do the import like this:
=let(
data, importrange("11EQ9lVbX6ioTECafiXTDxvoWRNDkEAEqj-KdI_-6BBB", "JGOOT Responses!AD3:AL"),
ifna(
if(B3 = "",
query(data, "where toDate(Col7) = date '2024-05-30' ", 0),
query(data, "where lower(Col9) contains '" & lower(B3) & "' ", 0)
),
"No Results Found"
)
)
The 'JGOOT Responses' tab has more than 50,000 blank rows at the bottom, which hurts performance. You should remove those blank rows, and edit the formulas in 'JGOOT Responses'!AM3:AN3 like this:
=arrayformula(iferror(datevalue(AI3:AI)))
To improve spreadsheet performance, see these optimization tips.