Digamos que sua célula de entrada seja A1 e suas células de saída sejam A3 para o nome e B3: B .. para o intervalo de saída (com o fim do intervalo B diminuindo até o número máximo de resultados que você espera para ver).
Para A3, coloque a fórmula =A1 .
Para B3, coloque a fórmula
=IFERROR(INDEX($N$1:$N$99,SMALL(IF($J$1:$J$99=$A$3,ROW($N$1:$N$99)-ROW($N$1)+1),ROWS($N$1:$N1))),"")
Esta é uma fórmula de matriz e precisa ser inserida com a combinação de teclas Controle - Shift - Enter . Você pode copiá-lo para a parte inferior do seu intervalo de saída.
Observe que a fórmula é codificada para uma lista de entrada com um máximo de 99 linhas. Você pode mudar isso para o tamanho que precisar. Embora seja possível fazer referência à coluna inteira (J: J e N: N), haverá um impacto no desempenho que você provavelmente desejará evitar.
Como a fórmula funciona
Trabalhando de dentro para fora, a fórmula primeiro compara o nome no qual a pesquisa deve ser executada (célula A3) com a lista completa de nomes (até 99 nomes no intervalo J1: J99). Essa comparação é mostrada na linha 6 da quebra de função mostrada abaixo.
O produto dessa comparação é uma matriz com valores True para uma correspondência e valores False para uma não correspondência, por exemplo, {Falso, Falso, Falso, Falso, Verdadeiro, Verdadeiro, Falso, etc. etc.}.
Em seguida, é feita uma comparação entre esse array com um array que pode ser considerado como os "números de linha" da lista de nomes: {1, 2, 3, 4, 5, 6, ... 99} . Essa comparação é feita pela instrução IF nas linhas 6-8 do gráfico de fórmulas.
A comparação é elemento a elemento. Se um elemento da matriz de comparação de nomes for igual a True, o IF retornará seu número de linha correspondente; se o elemento for igual a False, o IF retornará FALSE. Usando os dois arrays de exemplo acima, o resultado da instrução IF seria {False, False, False, False, 4, 5, False, ...}.
Continuando,afunçãoSMALL(iniciandonalinha8doesquemadefunções)éusadaparaobteromenorelementokthdestenovoarraydoIF.O"k" neste caso é fornecido pela expressão ROWS ($ N $ 1: $ N1), que simplesmente contará de 1 a 99 quando a fórmula inteira for copiada da linha 1 até a linha 99 (ROWS ($ N $ 1) : $ N1) = 1, ROWS ($ N $ 1: $ N2) = 2, e assim por diante).
Portanto, o SMALL primeiro encontrará o menor elemento do array produzido pelo IF, ignorando os elementos que são False. Em outras palavras, ele retornará o número da primeira linha em que o nome que está sendo comparado corresponde a um nome na lista de nomes de pesquisa. Em nosso exemplo, esse é o número 4, conforme mostrado na sexta coluna da tabela abaixo.
A etapa de finalização usa INDEX nos valores de pesquisa para buscar o elemento correspondente ao número da linha que foi calculado. Nesse caso, o quarto item no exemplo da lista de cores da pergunta é "amarelo". (O IFERROR garante que um espaço em branco seja mostrado quando a fórmula não conseguir encontrar uma correspondência.)
Esse foi o resultado produzido pela primeira cópia da fórmula completa. Quando é copiado para a próxima célula, a única coisa que muda no cálculo é o valor de "k" para a função SMALL, que avança para 2. E o segundo menor número de linha que foi encontrado é 5, o que produz o valor "branco".
