Converted Filter function into Query-CodePudding

I have created filter function to Filter some sort of data and it is working fine but later converted it to Query but i am stcuk on one thing that how to Filter the Date Column based on month values like (1,2,3,4,5,6,7,8,9,10,11,12). I have created a Data validation for all 12 months.

your help will be much appreciated.

Filter Function:

=IFERROR(FILTER('Sheet1'!B:K,'Sheet1'!D:D=M4,month('Sheet1'!E:E)=X4),"No Data Available")

Query Function:

=QUERY({'Sheet1'!B:K}, "SELECT * WHERE Col2 contains '"&J4&"' AND Col3 contains '"&M4&"' AND Col4 = month(date'"&text(X4,"M")&"') ORDER BY Col2 ASC",0)

Unable to make this Query line AND Col4 = month(date'"&text(X4,"M")&"')

CodePudding user response：

Try:

=QUERY({'Sheet1'!B:K}, "SELECT * WHERE Col2 contains '"&J4&"' AND Col3 contains '"&M4&"' AND month(Col4) ="&X4-1&" ORDER BY Col2 ASC",0)

CodePudding user response：

try:

=QUERY({Sheet1!B:K}, 
 "where Col2 contains '"&J4&"' 
    and Col3 contains '"&M4&"' 
    and month(Col4) 1 ="&X4&" 
  order by Col2", )

you can skip these:

select * if you want to get all columns you can just skip select argument
single quotes are needed only if sheet name contains a space
months in query start from 0 (as January) so we just add 1
when ordering its asc by default so we can skip it
zero 0 as the 3rd argument can be omitted