Fórmula genérica
=SMALL(IF(logical,ROW(list)-MIN(ROW(list))+1),n)
Resumo
Para obter a posição da enésima correspondência (por exemplo, o segundo valor correspondente, o terceiro valor correspondente, etc.), você pode usar uma fórmula baseada na função SMALL. No exemplo mostrado, a fórmula em G5 é:
=SMALL(IF(list=E5,ROW(list)-MIN(ROW(list))+1),F5)
Esta fórmula retorna a posição da segunda ocorrência de "vermelho" na lista.
Observação: esta é uma fórmula de matriz e deve ser inserida com control + shift + enter.
Explicação
Esta fórmula usa o intervalo nomeado "lista", que é o intervalo B5: B11.
O núcleo desta fórmula é a função SMALL, que simplesmente retorna o enésimo menor valor em uma lista de valores que correspondem aos números das linhas. Os números das linhas foram "filtrados" pela instrução IF, que aplica a lógica para uma correspondência. Trabalhando de dentro para fora, IF compara todos os valores no intervalo nomeado "lista" com o valor em B5, que cria uma matriz como esta:
(TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;TRUE)
O "valor se verdadeiro" é um conjunto de números de linhas relativos criados por este código:
ROW(list)-MIN(ROW(list))+1
O resultado é uma matriz como esta:
(1;2;3;4;5;6;7)
Veja esta página para uma explicação completa.
Com um teste lógico que retorna uma matriz de resultados, a função IF atua como um filtro - apenas os números de linha que correspondem a uma correspondência sobrevivem, o restante retorna FALSO. O resultado retornado por IF é assim:
(1;FALSE;FALSE;FALSE;5;FALSE;7)
Os números 1, 5 e 7 correspondem à localização do "vermelho" na lista.
Finalmente, SMALL retorna o enésimo menor item da lista, ignorando os valores FALSE. No exemplo, F5 contém 2, então SMALL retorna o segundo menor valor: 5.
Obtenha valor associado
Depois de ter a posição relativa da enésima correspondência, você pode usar essa posição com a função INDEX para retornar um valor associado.
