Resumo
Para extrair várias correspondências para células separadas, em linhas separadas, você pode usar uma fórmula de matriz baseada em INDEX e SMALL. No exemplo mostrado, a fórmula em E5 é:
(=IFERROR(INDEX(names,SMALL(IF(groups=E$4,ROW(names)-MIN(ROW(names))+1),ROWS($E$5:E5))),""))
Esta é uma fórmula de matriz e deve ser inserida com Control + Shift + Enter.
Depois de inserir a fórmula na primeira célula, arraste-a para baixo e para a frente para preencher as outras células.
Explicação
Observação: esta fórmula usa dois intervalos nomeados: "nomes" refere-se a C4: C11 e "grupos" refere-se a B4: B11. Esses nomes também são definidos na captura de tela acima.
A essência desta fórmula é a seguinte: estamos usando a função SMALL para obter um número de linha que corresponde a uma "enésima correspondência". Assim que tivermos o número da linha, simplesmente o passamos para a função de função INDEX, que retorna o valor daquela linha.
O truque é que SMALL está trabalhando com uma matriz que é construída dinamicamente por IF neste bit:
IF(groups=E$4,ROW(names)-MIN(ROW(names))+1)
Este trecho testa o intervalo nomeado "grupos" para o valor em E4. Se encontrado, ele retorna um número de linha "normalizado" de uma matriz de números de linha criada com esta parte da fórmula:
ROW(names)-MIN(ROW(names))+1
O resultado é uma matriz que contém números de linha onde há uma correspondência e FALSE onde não. A matriz é semelhante a esta:
(1; FALSO; FALSO; FALSO; FALSO; 6; FALSO)
Esta matriz vai para SMALL. O valor k para PEQUENO (enésimo) vem de um intervalo em expansão:
ROWS($E$5:E5)
Quando copiado para baixo na tabela de resultados, o intervalo se expande, fazendo com que k (enésimo) aumente. A função SMALL retorna cada número de linha correspondente, que é fornecido à função INDEX como row_num, com o intervalo nomeado "nomes" como a matriz.
Tratamento de erros
Quando ROWS retorna um valor para k que não existe, SMALL gera um erro #NUM. Isso acontece após todas as partidas terem ocorrido. Para suprimir o erro, usamos IFERROR para capturar o erro e retornar uma string vazia ("").
