Uma pergunta recorrente de usuários é se é possível retornar várias ocorrências de um valor numa lista utilizando o PROCV.
A resposta é não. A função PROCV só é capaz de retornar a primeira ocorrência de um valor, pesquisando-o na primeira coluna de uma referência. O mesmo vale para a função PROCH (pesquisa na primeira linha).
Já vimos aqui no blog duas alternativas para essa questão utilizando VBA: 0030-Turbinando o PROCV
Hoje veremos como é possível obter esse tipo de resultado utilizando funções de planilha. Essa não é única alternativa, podendo ser desenvolvidas outras abordagens.
Partiremos desse conjunto de dados para iniciarmos a análise.
Iremos pesquisar o valor "João", na primeira coluna e retornar o valor correspondente na coluna "Valor".
O primeiro valor que temos que conhecer é o número de vezes que "João" aparece no intervalo pesquisado.
Podemos obter facilmente esse resultado utilizando a função CONT.SE.
Esse parâmetro é importante, pois se quisermos buscar uma 6ª ocorrência no conjunto de dados obteremos uma mensagem de erro.
Em seguida, temos que estabelecer qual a ocorrência que desejamos retornar. No nosso exemplo, um número entre 1 e 5. Escolhemos o valor 3 aleatoriamente.
A próxima etapa é definir a coluna que será retornada ("Valor").
Sabendo a partir de qual coluna o valor será retornado, precisamos definir a posição desta coluna no intervalo de cabeçalhos.
Agora passaremos à construção da fórmula:
O primeiro passo é determinar o número das linhas nas quais o valor pesquisado ocorre.
=(A1:A13=F1)*LIN(A1:A13)
Essa fórmula retorna um vetor que mostra os números das linhas nas quais o valor pesquisado ocorre:
={0;2;0;0;5;6;0;0;9;0;0;12;0}
Ou seja, o valor "João" ocorre nas linhas 2;5;6;9;12 (5 ocorrências)
Se queremos a 3ª ocorrência, iremos procurar o 3º menor valor da sequência acima. Poderíamos utilizar a função MENOR, mas teríamos que utilizar um artifício para descontar os zeros. Ao invés disso, iremos pesquisar o 3º maior valor.
Deixaremos que o Excel faça essa conversão: (Nº Ocorrências - Ocorrência desejada + 1)
=MAIOR((A1:A13=F1)*LIN(A1:A13);F2-F3+1)
Se tentarmos aplicar a fórmula acima obteremos o valor de erro #NÚM!. Isso ocorre por que estamos lidando com fórmula matricial. Para evitarmos o uso de fórmulas matriciais, contornaremos o problema utilizando SOMARPRODUTO.
=SOMARPRODUTO(MAIOR((A1:A13=F1)*LIN(A1:A13);F2-F3+1))
A fórmula acima retorna o valor 6, indicando que a 3ª ocorrência do valor "João" ocorre na linha nº 6.
Obtendo a linha da coluna A e sabendo que o valor pesquisado se encontra na coluna de índice 3 (Coluna C), podemos utilizar a função DESLOC para retornar o valor desejado:
=DESLOC(A1;SOMARPRODUTO(MAIOR((A1:A13=F1)*LIN(A1:A13);F2-F3+1))-1;F5-1)
Criamos então uma fórmula capaz de retornar a k-ésima ocorrência de um valor num conjunto de dados.
Compreendido o raciocínio, incorporaremos à fórmula os valores intermediários calculados em F3 e F5, resultando em:
=DESLOC(A1;SOMARPRODUTO(MAIOR((A1:A13=F1)*LIN(A1:A13);CONT.SE(A1:A13;F1)-F2+1))-1;CORRESP(F3;A1:C1)-1)
A versão definitiva está disponível para download aqui.