google sheets: compare two columns and output matched keys-CodePudding

I want to generate a list of project keys that relate to relevant projects, that I retrieve from a query including different conditions (for simplicity I've added the queried list of relevant projects in A2:A5). The goal is to generate the output in A11, i.e., get all project keys related to the queried projects (excluding the current project) that are assigned the same client.

I have the following spreadsheet:

My current solution produces only a list of projects, but not the keys.

Query({A2:A5}, select"Col1 where Col1 matches '"& textjoin("|",True,query({C2:E6},"select Col1 where Col3=1"))&"'",0)

Any ideas how to solve this? NO arreyformula etc. since I don't actually "print" the queried projects anywhere.

CodePudding user response：

Try

=textjoin(",",,arrayformula(vlookup(Query({A2:A5}, "select Col1 where Col1 matches '"& textjoin("|",True,query({C2:E6},"select Col1 where Col3=1"))&"'",0),C2:E6,2,0)))

=> A12, B34, D78

or, to exclude current project

=textjoin(",",,arrayformula(vlookup(Query({A2:A5}, "select Col1 where Col1<>'"&A8&"' and Col1 matches '"& textjoin("|",True,query({C2:E6},"select Col1 where Col3=1"))&"'",0),C2:E6,2,0)))

=> B34, D78