Search code examples
google-sheetsgoogle-sheets-formula

google sheets: compare two columns and output matched keys

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: https://docs.google.com/spreadsheets/d/1nIkpVMf8PurJVB96EE-kOltUqhwFHOydi2uvuJq2yMU/edit?usp=sharing enter image description here

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.

Solution

  • 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