In Google Sheets, suppose I have rows like:

name team memberships
Ben operations legal
Kate legal UK residents infrastructure growth
Carlos operations senior management legal
Tina operations UK residents senior management
Pierre infrastructure operations legal growth
Amanda UK residents infrastructure

How do I get the list of people who are both UK residents, and also in infrastructure? (The output should be the list: Kate, Amanda)

(For convenience, I put this on Sheets here: https://docs.google.com/spreadsheets/d/1bNByBeM_FKuzS-MLRYOsHIP1YRs4bOH7w0c9P8b_jHs/edit?usp=sharing )

I have seen uses of match() and index(), but I can't seem to find something that lets me match logically on multiple cells within each row, while still maintaining knowledge about the rows themselves. index(match(...)) seems to allow me to do this for a single output name, but not for a set of names.


1 Answer 1


Use a query smush, like this:

      transpose( query( transpose(substitute(A2:F, " ", "_")), "", 9^9 ) ), 
      "(.+?) (.+)" 
    "select Col1 
     where lower(Col2) contains 'infrastructure' 
     and lower(Col2) contains 'uk_residents' ", 

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.