Can you have a constrained QUERY within an Arrayformula?

Question

I have a query that needs to be INDEPENDENTLY applied to each cell in column A. The Query has where column condition that contains value in "A:A". Is there way to do this inside an arrayformula? I have tried constraining the return to "limit 1" (there is only one column selected so it will not expand into adjacent cells) or array_constrain the query to 1 row + 1 cell, but it only seems to return the first value of the query selected column.

Example query returns 1 cell of value (without header):

=query(D:E, "select D where E contains '"&A:A&"' limit 1, 0)

Wrap into force 1 cell

=array_constrain(query above,1,1) constrains to 1 cell as well

Wrap in arrayformula but it will only return first value of D

=arrayformula(if(A:A<>"",apply the constrained query,0)

Any ideas?

Answer 1

I think this won't be possible. But, If the condition is just contains, then just use VLOOKUP. If the values in column A are unique. You could use

    
 matches '"JOIN("|", A1:A50)"'