How do I write a Query in G Sheets with multiple criteria, which also references a cell?-CodePudding

I am trying to write a Query in Google Sheets using the following formula:

=QUERY(Input!A2:AD,"SELECT Col1, Col4, Col5, Col6, Col7, Col12, Col13 WHERE Col12 > 200 AND Col30 = '"&B3&"' ORDER BY Col13 DESC LIMIT 10")

I have also tried:

=QUERY(Input!A2:AD,"SELECT A, D, E, F, G, L, M WHERE L > 200 AND AD = '"&B3&"' ORDER BY M DESC LIMIT 10")

Can anyone help with where I am going wrong please?

Thanks!

I tried the two formulas listed above.

I was expecting the formula to produce a list of 10 results, ranked in order highest to lowest

CodePudding user response：

try:

=QUERY({Input!A2:AD}, 
 "select Col1,Col4,Col5,Col6,Col7,Col12,Col13 
  where Col12 > 200 
    and Col30 = '"&TO_TEXT(B3)&"' 
  order by Col13 desc 
  limit 10", )

assuming Col30 is formatted as plain text and not as a valid date

if Col30 contains valid dates try:

=INDEX(QUERY({Input!A2:AD, TEXT(Input!AD2:AD, "mmmm e")}, 
 "select Col1,Col4,Col5,Col6,Col7,Col12,Col13 
  where Col12 > 200 
    and Col31 = '"&TO_TEXT(B3)&"' 
  order by Col13 desc 
  limit 10", ))

CodePudding user response：

Maybe that AD column has numbers in it??, then you can remove the single quotations mark before and after "&B3&"

(Just FYI: If you use Col1, Col2, you should set the range between: {Input!A2:A})