Fórmula genérica
(=TEXTJOIN(", ",TRUE,IF(rng1=E5,rng2,"")))
Resumen
Para buscar y recuperar múltiples coincidencias en una lista separada por comas (en una sola celda), puede usar la función SI con la función TEXTJOIN. En el ejemplo que se muestra, la fórmula en F5 es:
(=TEXTJOIN(", ",TRUE,IF(group=E5,name,"")))
Esta es una fórmula de matriz y debe ingresarse con control + shift + enter.
Esta fórmula utiliza los rangos con nombre "nombre" (B5: B11) y "grupo" (C5: C11).
Explicación
El núcleo de esta fórmula es la función SI, que "filtra" los nombres en la tabla por color de esta manera:
IF(group=E5,name,""))
La prueba lógica verifica cada celda en el "grupo" de rango nombrado para el valor de color en E5 (rojo en este caso). El resultado es una matriz como esta:
(FALSE;FALSE;TRUE;TRUE;FALSE;FALSE;TRUE)
Ese resultado se utiliza a su vez para filtrar nombres del rango con nombre "nombre":
("Matt";"Sally";"Jude";"Aya";"Elle";"Linda";"George")
Para cada VERDADERO, el nombre sobrevive, para cada FALSO, SI devuelve una cadena vacía ("").
El resultado de las miradas IF es esta matriz:
("";"";"Jude";"Aya";"";"";"George")
que entra en la función TEXTJOIN como text1.
TEXTJOIN está configurado para usar una coma como delimitador y para ignorar los valores vacíos. El resultado final es esta cadena de texto:
"Jude, Aya, George"
Varias condiciones
No puede usar las funciones Y u O en una fórmula de matriz como esta porque solo devuelven un único resultado. Puede usar lógica booleana como esta para AND:
=TEXTJOIN(", ",TRUE,IF((condition1)*(condition2),name,""))
Explicado con más detalle aquí.
