Resumo
Para extrair várias correspondências para células separadas, em colunas separadas, você pode usar uma fórmula de matriz baseada em INDEX e SMALL. No exemplo mostrado, a fórmula em F5 é:
(=IFERROR(INDEX(names,SMALL(IF(groups=$E5,ROW(names)-MIN(ROW(names))+1),COLUMNS($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
Nota: esta fórmula usa dois intervalos nomeados: "nomes" refere-se a C5: C11 e "grupos" refere-se a B5: 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 gerar um número de linha correspondente 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=$E5,ROW(names)-MIN(ROW(names))+1)
Este trecho testa o intervalo nomeado "grupos" para o valor em E5. Se encontrado, ele retorna um número de linha de uma matriz de números de linhas relativos criados com:
ROW(names)-MIN(ROW(names))+1
O resultado final é uma matriz que contém números onde há uma correspondência e FALSE onde não:
(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:
COLUMNS($E$5:E5)
Quando copiado 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 COLUMNS 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, envolvemos a fórmula na função IFERROR para capturar os erros e retornar uma string vazia ("").
