How can I query importrange where Col values = any of the range specified in another sheet?

For example: =QUERY(IMPORTRANGE("/18UF6ZR19iWulTMHT3lg6mv0NLrghItzW6bRT_p7bzsA/","Data!A2:E"),"select Col4,Col3,Col1,Col2 where Col4 = '"&Helper!A2:A&"'",0)

This workbook has 3 sheets in it:

1. Data! = Data source
2. Helper! = Range required for the query to search for
3. Testing! = Is where I would like the data to return (Testing!A2 has the formula I have tried but it is not working as expected)

https://docs.google.com/spreadsheets/d/18UF6ZR19iWulTMHT3lg6mv0NLrghItzW6bRT_p7bzsA/edit?usp=sharing

I would like it to return data for the range Helper!A2:A but it is currently only returning the data from Helper!A2 I'm not sure what is going wrong as no errors return.

CodePudding user response：

=QUERY(IMPORTRANGE("/18UF6ZR19iWulTMHT3lg6mv0NLrghItzW6bRT_p7bzsA/","Data!A2:E"),"select Col4,Col3,Col1,Col2 where Col4 matches '"&TEXTJOIN("|",TRUE,Helper!A2:A)&"'",0)

CodePudding user response：

use:

=QUERY(IMPORTRANGE("18UF6ZR19iWulTMHT3lg6mv0NLrghItzW6bRT_p7bzsA","Data!A2:E"),
 "select Col4,Col3,Col1,Col2 
  where Col4 matches '"&TEXTJOIN("|", 1, Helper!A2:A)&"'", 0)