google-sheetsgoogle-sheets-formula

Select rows that contain a specific dropdown value


I have the following Google Spreadsheet file:

A "People" sheet:
The "People" sheet

and a "Projects" sheet:
The "Projects" sheet

As you can see, I've added a "Projects" dropdown to the People sheet using data validation so I can indicate what projects each person is on.

Now what I want is to have some kind of "back reference" in the Projects sheet, so I can see all the people that are on each project, using the data from the People sheet.

On the second screenshot, you can see one of my failed attempts using the following formula : =TEXTJOIN(", "; TRUE; QUERY(People!$A$2:$B; "SELECT A WHERE B CONTAINS '" & A2 & "'")). The problems are that 1) an error #N/A is returned for projects that have nobody on it, and 2) most importantly some people are incorrectly matched. For example, for Project 1, CALLISON Edgar is matched while he's not on the project, probably because his project, 31, has a 1 in it and thus is matched by the formula.

How I can I fix this?


Solution

  • You can use IFNA to hide the error and MATCHES with \b word boundaries instead of CONTAINS.

    =IFNA(TEXTJOIN(", "; TRUE; QUERY(People!$A$2:$B; "SELECT A WHERE B MATCHES '.*\b" & A2 & "\b.*'")))