53 Função PROCV Como retornar todos os resultados

Função PROCV Como retornar todos os valores. A Função PROCV é excelente para procurar um valor em uma tabela baseado em um termo de pesquisa. Mas, se em sua tabela, o termo procurado aparecer mais de uma vez, a Função PROCV não irá conseguir retornar todas as correspondências. Como alternativa à Função PROCV, pode-se utilizar a Função ÍNDICE para retornar todas as ocorrências para o termo procurado.

Para recordar como funciona a Função PROCV leia:

Função PROCV Como usar no Excel

Função PROCV Como retornar todos os valores

Na planilha abaixo pode-se perceber que, na relação de clientes, o nome Marcos aparece três vezes. O desafio é elaborar uma fórmula que permita retornar todos os produtos por ele adquiridos. Se utilizar a Função PROCV você verá que ela irá retornar apenas a primeira ocorrência.

Então, para que o Excel seja capaz de retornar todos os produtos comprados por Marcos, uma solução é desenvolver uma fórmula matricial que verifique linha a linha e apresente todos os resultados encontrados.

A célula E6 contém a seguinte fórmula:

=SEERRO(ÍNDICE($A$6:$B$15;MENOR(SE($A$6:$A$15=$E$5;LIN($A$6:$A$15)-5);LIN(A1));2);"")

Ao final da fórmula deve-se pressionar CTRL+SHIFT+ENTER para torná-la matricial

Para retornar os outros resultados basta copiar a fórmula da célula E6 para baixo até a célula E15.

Ao copiar a fórmula para baixo a referência LIN(A1) se atualiza dinamicamente para LIN(A2), LIN(A3) e assim por diante. Desta forma a função irá retornar o 2º resultado, o 3º resultado, e assim sucessivamente.

Como esta fórmula funciona

A base desta fórmula é a Função ÍNDICE. Esta função irá procurar no intervalo de células A6:B15 o nome contido na célula E5, Marcos, e então retornar os valores contidos na coluna B que correspondam a Marcos.

A sintaxe da Função ÍNDICE é:

=ÍNDICE(matriz;núm_linha;[núm_coluna])

Diante disso para completar o argumento núm_linha é necessário utilizar a Função MENOR em conjunto com a Função SE e LIN do Excel.

Para recordar como funciona a Função ÍNDICE leia:

Função ÍNDICE CORRESP Como usar no Excel

Como desenvolver esta fórmula de pesquisa

Primeiro passo:  Escrever a Função SE

A Função SE verifica quais valores no intervalo A6:A15 = Marcos e retorna o número da linha correspondente.

=SE($A$6:$A$15=$E$5;LIN($A$6:$A$15)-5

O nome Marcos aparece nas linhas 2, 7 e 9. Estes números correspondem as linhas dentro do intervalo A16:A15 e não aos números das linhas da planilha. Por esse motivo diminuímos 5 linhas no trecho LIN($A$6:$A$15)-5 que correspondem ao número de linhas que não fazem parte do intervalo.

Segundo passo: Acrescentar a Função MENOR

 A Função MENOR retorna o menor valor k-ésimo de uma matriz. Sua sintaxe é:

=MENOR(Matriz;K)

Se K = 1, a função irá retornar o menor valor da matriz; se K = 2, o segundo menor valor e assim por diante.

A Matriz de valores é preenchida com o resultado obtido através da Função SE. Já o argumento K é obtido com a Função LIN através do trecho LIN(A1). Então neste ponto a fórmula é escrita da seguinte forma:

=MENOR(SE($A$6:$A$15=$E$5;LIN($A$6:$A$15)-5);LIN(A1))

A fórmula acima determina o número das linhas para cada ocorrência de Marcos no intervalo A16:A15. Após o processamento da fórmula temos o seguinte resultado:

=MENOR({2,7,9};1)

Diante disso temos que Marcos ocorre nas linhas 2, 7 e 9 no intervalo A16:A15.

Terceiro passo: Desenvolver a Função ÍNDICE

Após elaborar a fórmula capaz de encontrar o número da linha para cada ocorrência de Marcos, deve-se utilizá-la para preencher o argumento núm_linha da Função ÍNDICE.

=ÍNDICE($A$6:$B$15;MENOR(SE($A$6:$A$15=$E$5;LIN($A$6:$A$15)-5);LIN(A1));2)

Ao final da fórmula deve-se pressionar CTRL+SHIFT+ENTER para torná-la matricial. Arraste a fórmula para baixo para apresentar os demais resultados.

Passo a passo do processamento ocorrido na célula E6:

=ÍNDICE($A$6:$B$15;MENOR(SE($A$6:$A$15=$E$5;LIN($A$6:$A$15)-5);LIN(A1));2)

=ÍNDICE($A$6:$B$15;MENOR({2,7,9;1});2)

=ÍNDICE($A$6:$B$15;2;2)

=Ultrabook 14""

Quarto passo: Adicionar a Função SEERRO

Para evitar que o Excel retorne o erro #NÚM! e deixar a planilha mais profissional utilize a Função SEERRO:

=SEERRO(ÍNDICE($A$6:$B$15;MENOR(SE($A$6:$A$15=$E$5;LIN($A$6:$A$15)-5);LIN(A1));2);"")

Leitura recomendada: Função SEERRO Como esconder erros no Excel

Agora é com você!

Baixe aqui a planilha utilizada neste exemplo e pratique.

É proibido a reprodução parcial ou integral dos artigos deste blog. Copiar na íntegra qualquer conteúdo aqui publicado, implica em crime previsto no Código Penal

E você, conhece outros métodos para retornar todos os valores no Excel?

Se você gostou, compartilhe na sua rede social: Facebook, Twitter ou Google+ usando os botões abaixo.