-1

https://docs.google.com/spreadsheets/d/1HeldkN3k1H-WfyCusjOj4gocBtSeHkwKhDbNYDyYr5I/edit?usp=sharing

Appending the two queries in S2 does not work as shown in this answer.

={QUERY(C2:J, "SELECT D, I WHERE C <= date '"&TEXT(N1,"yyyy-mm-dd")&"'",0);QUERY(C2:J, "SELECT E, J WHERE C <= date '"&TEXT(N1,"yyyy-mm-dd")&"'",0)}

enter image description here

user1304598
  • 131
  • 9

3 Answers3

1

Try:

=arrayformula({QUERY(C2:J, "SELECT D, I WHERE D IS NOT NULL AND C <= date '"&TEXT(N1,"yyyy-mm-dd")&"'",0);QUERY(C2:J, "SELECT E, J WHERE D IS NOT NULL AND C <= date '"&TEXT(N1,"yyyy-mm-dd")&"'",0)})
Aresvik
  • 4,484
  • 1
  • 5
  • 18
1

There are blank rows after the first query and the second query starts at somewhere row 50000.

Add a wrapper query to exclude the blank rows.

=QUERY({QUERY(C2:J, "SELECT D, I WHERE C <= date '"&TEXT(N1,"yyyy-mm-dd")&"'",0);QUERY(C2:J, "SELECT E, J WHERE C <= date '"&TEXT(N1,"yyyy-mm-dd")&"'",0)},"select * where Col1 is not null",0)
idfurw
  • 5,727
  • 2
  • 5
  • 18
0

use the proper way:

=QUERY({D:D, I:I, C:C; E:E, J:J, C:C}, 
 "select Col1,Col2 
  where Col2 matches '\d+' 
    and Col3 <= date '"&TEXT(N1, "yyyy-mm-dd")&"'", 0)

enter image description here

player0
  • 124,011
  • 12
  • 67
  • 124