Mais de 1000 ALUNOS

Formação Completa em Excel!

Início » Blog Guia do Excel » Procv com mais de um resultado matricial

Procv com mais de um resultado matricial

Neste artigo é demonstrado como fazer um PROCV que retorne mais de um resultado utilizando matricial. Veja mais em https://www.guiadoexcel.com.br/avancado/

O PROCV é uma das funções de pesquisa e referência do Excel para localizar informações de uma lista em outra. Por exemplo buscar o nome de um cliente baseado no seu código.

Esta técnica foi disponibilizada pelo amigo Felipe Matsudo Garcia ao qual agradecemos pela valiosa contribuição.

Para realizar a busca com mais de um resultado temos um exemplo que será resolvido de duas formas diferentes, uma com coluna auxiliar e outra sem a coluna auxiliar.

Siga abaixo o exemplo e a explicação de cada uma. O download está no final do artigo e no começo dele você tem também o vídeo explicando a criação e utilização detalhada das fórmulas.

Problema

Temos uma lista aonde o código do produto se repete, mas não a sua descrição. O objetivo é retornar todos os produtos pelo seu código.

Procv com mais de um resultado com coluna auxiliar

A primeira solução consiste em utilizarmos uma lista auxiliar para indicar qual o “número” do item. Por exemplo, o primeiro item 3023 seria o item 1, o segundo 3023 o 2 e o terceiro o 3.

Desta forma é utilizada esta coluna como auxiliar para definir qual dos itens deve ser retornado naquela situação.

Criando a coluna auxiliar

Ao lado da descrição foi criada uma coluna com a fórmula: =CONT.SE($C$5:C5;C5).

A função CONT.SE faz uma contagem de células por um determinado critério, no nosso caso o critério é o código do produto.

$C$5:C5: Intervalo de contagem, aonde serão contados os itens conforme o critério. Perceba que o primeiro é travado em $C$5, isso foi feito para que ao arrastar o início do intervalo não seja alterado, mas o final dele sim, aumentando a lista na medida em que cresce.

Assim, a contagem será somente dos itens acima daquela linha que sejam daquele código, criando um sequencial único para cada produto.

C5: É o critério que será utilizado, no caso o código do produto.

Veja que a medida em que a função foi arrastada para baixo o intervalo de contagem foi alterado.

Após a criação da coluna auxiliar já podemos partir para a fórmula.

A fórmula utilizada é uma fórmula matricial, ou seja, ela atua de forma diferente de uma fórmula padrão do Excel, em outro artigo iremos falar mais a respeito e dar uma introdução clara sobre matriciais e algumas outras aplicações, por enquanto vamos focar na solução.

A fórmula utilizada foi: =SEERRO(ÍNDICE($D$5:$D$11;MENOR(SE($C$5:$C$11=$G5;LIN($D$5:$D$11)-LIN($D$5)+1);$E5));””) aplicar a função com CTRL+SHIFT+ENTER.

Abaixo a explicação das partes que compõe esta fórmula:

ÍNDICE($D$5:$D$11 : A função ÍNDICE é uma função de busca parecida com a função PROCV. Veja detalhes de como utilizá-la neste artigo: Índice Corresp Excel – Como utilizar Índice Corresp no Excel

No caso desta primeira parte temos a seleção do que desejamos que seja retornado, no caso a descrição dos produtos que se encontra neste intervalo.

MENOR(SE($C$5:$C$11=$G5;LIN($D$5:$D$11)-LIN($D$5)+1) : Esta é a parte matricial da função. A lógica é a seguinte, SE o valor em C5:C11 for igual ao valor em G5, o sistema retorna o número da linha correspondente.

Veja que o resultado deste cálculo é FALSO, FALSO, 3,4,5, FALSO, FALSO. Os valores 3,4,5 são referentes ás linhas aonde o código do produto foi encontrado, e o FALSO é aonde não foi encontrado, estes são descartados.

;$E5)) : Este parâmetro está ligado com a função MENOR, e aponta para a lista auxiliar, retornando o 1,2, 3, etc. Conforme o número que estiver na lista.

No caso deste item, se for o 1 ele retornaria 3, se for 2 ele retornará o 4 e se for o 3 o 5, conforme temos na nossa lista retornada pelo SE.

=SEERRO( : Esta função retorna um valor padrão caso seja identificado um erro no retorno da função, no caso desta está com “” no retorno.

Observação: Use o CTRL+SHIFT+ENTER para que a função seja lida como matricial.

Procv com mais de um resultado sem coluna auxiliar

Nesta outra solução não foi usada a temos uma função praticamente igual, mas com a diferença de não utilizar a coluna auxiliar.

A fórmula utilizada é:

=SEERRO(ÍNDICE($D$5:$D$11;MENOR(SE($C$5:$C$11=$G5;LIN($D$5:$D$11)-LIN($D$5)+1);CONT.SE($G$5:G5;G5)));””) aplicar a função com CTRL+SHIFT+ENTER.

Vamos replicar abaixo as pares que são semelhantes e também vamos explicar a parte do CONT.SE no final.

Abaixo a explicação das partes que compõe esta fórmula:

No caso desta primeira parte temos a seleção do que desejamos que seja retornado, no caso a descrição dos produtos que se encontra neste intervalo.

;$E5)) : Este parâmetro está ligado com a função MENOR, e aponta para a lista auxiliar, retornando o 1,2, 3, etc. Conforme o número que estiver na lista.

No caso deste item, se for o 1 ele retornaria 3, se for 2 ele retornará o 4 e se for o 3 o 5, conforme temos na nossa lista retornada pelo SE.

CONT.SE($G$5:G5;G5) : Nesta última parte aonde teríamos o E5 que se referiria á coluna auxiliar, nós temos a própria fórmula da coluna auxiliar.

Desta forma o CONT.SE continua funcionando da mesma forma que na coluna auxiliar e o retorno é sempre o número sequencial até aquele ponto, que é a contagem de códigos semelhantes até aquela linha.

=SEERRO( : Esta função retorna um valor padrão caso seja identificado um erro no retorno da função, no caso desta está com “” no retorno.

Observação: Use o CTRL+SHIFT+ENTER para que a função seja lida como matricial.

Baixe a planilha

Você pode se interessar por: Como usar o PROCV no Excel

Abraço

Marcos Rieper