Want to use query import range with array dynamic cell value

Question

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

Answer 1

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),),)))