Want to use query import range with array dynamic cell value-CodePudding

This is My formula but cell reference not work on array AB2:AB and AC2:AC

=Arrayformula(if(A2:A<>"",iferror(query(importrange("link", "Work List!$A$4:$H"), "select Col1, Col2 where lower(Col1) matches '.*"&lower(AB2:AB)&".*' and not lower(Col1) matches '.*"&lower(AC2:AC)&".*' ", -1),""),""))

Please Guide

Working on big data import range sheet have 256 rows and current sheet array have 956rows

CodePudding user response：

arrays are not supported on that spot within QUERY. use:

=ARRAYFORMULA(IF(A2:A<>"", IFERROR(QUERY(
 IMPORTRANGE("link", "Work List!A4:H"), 
 "select Col1,Col2 
  where     lower(Col1) matches '.*"&LOWER(TEXTJOIN(".*|.*", 1, AB2:AB))&".*' 
    and not lower(Col1) matches '.*"&LOWER(TEXTJOIN(".*|.*", 1, AC2:AC))&".*' ", -1), ), ))

update:

=BYROW(A2:A, LAMBDA(x, IF(x<>"",IFERROR(QUERY(
 IMPORTRANGE("1SKXg3dFp9a83LQfURrF-QlAJvaupERvnqrdPv2CHMXw",  "Sample Data!A2:B"), 
 "select Col1,Col2 where lower(Col1) matches '.*"&LOWER(OFFSET(x,,1))&".*' 
  and not lower(Col1) matches '.*"&LOWER(OFFSET(x,,2))&".*' ", 0),),)))