1

I am trying to query values from a different sheet using a range from the current sheet. Is there a way to simplify the formula and get rid of all the ORs? Like maybe using a FOR loop?

=QUERY(IMPORTRANGE("https://docs.google.com/spreadsheets/d/sample", "Production!A:P"), 
"select Col1, Col6, Col8, Col16 where 
(Col8 contains '"&Tracking!B2&"')  or (Col8 contains '"&Tracking!B3&"')  or
(Col8 contains '"&Tracking!B4&"')  or (Col8 contains '"&Tracking!B5&"')  or 
(Col8 contains '"&Tracking!B6&"')  or (Col8 contains '"&Tracking!B7&"')  or 
(Col8 contains '"&Tracking!B8&"')  or (Col8 contains '"&Tracking!B9&"')  or 
(Col8 contains '"&Tracking!B10&"') or (Col8 contains '"&Tracking!B11&"') or 
(Col8 contains '"&Tracking!B12&"') or (Col8 contains '"&Tracking!B13&"') or 
(Col8 contains '"&Tracking!B14&"') or (Col8 contains '"&Tracking!B15&"') or 
(Col8 contains '"&Tracking!B16&"') or (Col8 contains '"&Tracking!B17&"') or 
(Col8 contains '"&Tracking!B18&"') or (Col8 contains '"&Tracking!B19&"') or 
(Col8 contains '"&Tracking!B20&"') or (Col8 contains '"&Tracking!B21&"') or 
(Col8 contains '"&Tracking!B22&"') or (Col8 contains '"&Tracking!B23&"') or 
(Col8 contains '"&Tracking!B24&"') or (Col8 contains '"&Tracking!B25&"') or 
(Col8 contains '"&Tracking!B26&"') or (Col8 contains '"&Tracking!B27&"') or 
(Col8 contains '"&Tracking!B28&"') or (Col8 contains '"&Tracking!B29&"') or 
(Col8 contains '"&Tracking!B30&"')", 1)
0

try like this:

=FILTER(QUERY(IMPORTRANGE("15YS4mri0m23Z5ai0D3xrGoFw0IYpXNrF-vOpaVYZP_I", "Production!A2:P"),
 "select Col1,Col6,Col8,Col16", 0), 
 REGEXMATCH(IMPORTRANGE("15YS4mri0m23Z5ai0D3xrGoFw0IYpXNrF-vOpaVYZP_I", "Production!H2:H"), 
 ".*"&TEXTJOIN(".*|.*", 1, Tracking!B2:B30)&".*"))

0

  • Thank you but when I use the first example I get the error: FILTER has mismatched range sizes. Expected row count: 6192. column count: 1. Actual row count: 1, column count: 1. And when I use the second example I get error: No matches are found in FILTER evaluation. – Exission Exission Jul 17 at 5:18
  • can you share a copy of your sheet? – user0 Jul 17 at 5:51
  • This is the book I am querying: docs.google.com/spreadsheets/d/… And this is the sheet I am using the query statement on("In" sheet, A1): docs.google.com/spreadsheets/d/… Book2 is querying values from Book1 based on input entered in the Tracking sheet. Thanks. – Exission Exission Jul 17 at 12:48
  • @ExissionExission answer updated – user0 Jul 17 at 14:00
  • Thanks again for all your help. The code almost works! However it sometimes queries similar items. For instance, I added row 1001 and 1002 to docs.google.com/spreadsheets/d/… When I go to: docs.google.com/spreadsheets/d/… and add 'Black, Leg Leveler' as an item in sheet Tracking, the query formula also queries 'Black, Leg Leveler Socket'. Could you please help me with that? – Exission Exission Jul 17 at 14:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.