Fórmula genérica
(=TEXTJOIN(", ",TRUE,IF(rng1=E5,rng2,"")))
Resumo
Para pesquisar e recuperar várias correspondências em uma lista separada por vírgulas (em uma única célula), você pode usar a função IF com a função TEXTJOIN. No exemplo mostrado, a fórmula em F5 é:
(=TEXTJOIN(", ",TRUE,IF(group=E5,name,"")))
Esta é uma fórmula de matriz e deve ser inserida com control + shift + enter.
Esta fórmula usa os intervalos nomeados "nome" (B5: B11) e "grupo" (C5: C11).
Explicação
O núcleo desta fórmula é a função IF, que "filtra" os nomes na tabela por cor, assim:
IF(group=E5,name,""))
O teste lógico verifica cada célula no intervalo nomeado "grupo" para o valor de cor em E5 (vermelho neste caso). O resultado é uma matriz como esta:
(FALSE;FALSE;TRUE;TRUE;FALSE;FALSE;TRUE)
Esse resultado é usado por sua vez para filtrar nomes do intervalo nomeado "nome":
("Matt";"Sally";"Jude";"Aya";"Elle";"Linda";"George")
Para cada TRUE, o nome sobrevive, para cada FALSE, IF retorna uma string vazia ("").
O resultado da aparência de IF é esta matriz:
("";"";"Jude";"Aya";"";"";"George")
que vai para a função TEXTJOIN como text1.
TEXTJOIN é configurado para usar uma vírgula como delimitador e para ignorar valores vazios. O resultado final é esta string de texto:
"Jude, Aya, George"
Múltiplas condições
Você não pode usar as funções AND ou OR em uma fórmula de matriz como esta porque elas retornam apenas um único resultado. Você pode usar a lógica booleana assim para AND:
=TEXTJOIN(", ",TRUE,IF((condition1)*(condition2),name,""))
Explicado com mais detalhes aqui.
