: Coloque seu e-mail aqui para receber GRATUITAMENTE o meu EBOOK - - 10 Funções que podem fazer de você um perito em EXCEL
Excel na Web

43 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 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:

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: Como corrigir erros no Excel

Agora é com você!


É 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.

Ricardo Fialkoski é formado em Sistemas de Informação, gosta de programação orientada a objetos mas tem como Hobby desenvolver Soluções avançadas em Excel. e VBA.

Comentário(s):

43 comentários :

  1. Ótima explicação. Consigo usar esta formula para fazer uma busca em mais de uma planilha?

    ResponderExcluir
    Respostas
    1. Boa noite, para obter este resultado teria que tentar algo aplicando o conceito de procurar em varias planilhas e unir a esta ideia de mostrar todos os resultados. Para procurar em multiplas planilhas veja: http://erredoze.blogspot.com/2013/07/procv-como-procurar-em-varias-planilhas.html.

      Obrigado pela visita!

      Excluir
  2. Tem como retornar apenas os valores únicos??? Descartando as repetidas.

    ResponderExcluir
    Respostas
    1. Boa noite Dajano, da forma como esta fórmula foi desenvolvida não. Ela retorna todos os valores sem exceção.

      Excluir
  3. Ricardo,
    Como faço para retornar os valores em colunas e não em linhas.
    posso mandar pra você uma planilha que estou tentando fazer?

    ResponderExcluir
    Respostas
    1. Bom dia! Por gentileza utilize o formulario de contato para detalhar a sua duvida, e conversamos por e-mail pode ser? Segue link do formulario: http://www.excelnaweb.com.br/p/blog-page_18.html

      Excluir
  4. Cara demais.. salvou minha vida.
    Eu tinha duas colunas (lotes 1 a 150) e outra de números aleatórios (360mil o.O).
    Precisava colocar os números aleatórios em 25 planilhas com 6 colunas de lotes.
    Com essa fórmula isso ficou fácil.
    Tu tinha que ser canonizado por dividir isso de maneira entendível como fez.

    ResponderExcluir
    Respostas
    1. Ricardo, muito obrigado pelo comentário! Isso é oxigênio para o Excelnaweb! Me sinto honrado em saber que de alguma forma pude contribuir na solução do seu problema.

      Um forte abraço

      Excluir
  5. como faço para criar aquele seta para filtros os nomes(suelen, marcos e etc...)

    ResponderExcluir
    Respostas
    1. Ola você consegue colocar as setas através do auto filtro do Excel. De uma olhada neste post que mostro como usar o auto filtro no Excel:

      http://www.excelnaweb.com.br/2014/04/autofiltro-como-filtrar-dados-no-excel.html

      Um abraço

      Excluir
  6. Olá Ricardo! Primeiramente, meus parabéns pelo post, muito didático e prático!
    A sua solução é quase o que estou procurando.
    No meu caso, ao invés de Produto eu tenho Valor, mas o que preciso é que em uma célula tenha a soma de todas as ocorrências de Valor de um mesmo cliente. Sou bem iniciante em Excel, por isso pergunto, é possível inserir uma simples função de soma na sua fórmula, ou teria que elaborar algo mais complexo, como uma tabela ou função intermediária para antes trazer todos os valores, calcular e depois trazer o total?

    Desde já, obrigado pela atenção.

    ResponderExcluir
    Respostas
    1. Ola Vinícius, acredito que você possa usar a função SOMASE. A Função SOMASE serve para somar um intervalo de dados que atendam a um critério. Você pode usar da seguinte foma: =SOMASE(coluna com os clientes;"cliente que quer somar";coluna que quer somar). Eu ainda não ensinei como usar a Função SOMASE no Excel, mas é muito interessante você pesquisar a respeito e conhecer como funciona a Função SOMASE. Se precisar entre em contato comigo pelo link do menu acima "Contato". Um abraço!

      Excluir
  7. Bom dia Ricardo!

    Muito bom o teu post! Exatamente o que eu precisava...

    Só tem uma diferença e estou me quebrando pra fazer...

    Ao invés de ser uma referência (Marcos = Marcos), eu gostaria de usar duas referência.

    Por exemplo, usando teu exemplo, SE ( E ( Marcos = Marcos ; Preço > 200 ) ; LIN ( ......

    Entendeu? Eu preciso que ele confira duas coisas na tabela!!! Eu tento apenas usar a função E ou um SE dentro de outro SE e ele se perde!

    Tem como me ajudar?

    ResponderExcluir
    Respostas
    1. Ola neuwald! Nesse caso seria necessário fazer uma espécie de Procv com duas condições e depois aplicar o conceito do Procv para retornar todos os valores. Complicado hein!

      Pensando aqui rapidamente não consegui em Funções que pudessem ser aplicadas sem usar VBA.

      Mas vou verificar melhor e se eu achar uma forma de fazer um Procv com duas condições retornar todos os valores eu posto aqui no blog combinado?

      Abraços, Ricardo.

      Excluir
    2. Ricardo, consegui! Eu usei um SE dentro de outro SE e deu certo!

      O problema é que são +- 180 celulas em 12 planilhas (+- 2000 celulas) que fazem essa busca em umas 150 linhas... Ficou muito pesado :/

      Obrigado pela atenção!

      Excluir
    3. Sensacional neuwald! Exatamente isso!

      Uma Função SE dentro SE para fazer um PROCV com duas condições retornar todos os valores!

      Abraços!

      Excluir
    4. Bom dia Ricardo e Neuwald!

      Muito legal o post, parabéns. Estou com o mesmo problema do Neuwald, só que ainda não consegui resolver. Podem me ajudar?

      No meu caso, eu preciso que sejam procuradas e verificadas 3 condições, por exemplo =se(e(cnpj=cnpj;número=número;palavra=palavra);ele deve me dizer que a informação está repetida, para que não seja adicionada novamente a mesma informação; caso contrário pode exibir "").

      Obrigada pela atenção!

      Abraços!

      Excluir
    5. Olá Mônica!

      Pois então, pelo que eu me lembro (não estou com a planilha aqui) mas eu usei:

      se(numero=numero;se(nome=nome;se(cpf=cpf;1;0);0);0)

      Alguma coisa do gênero, então se todos forem iguais, ele retorna 1, se algum for diferente ele retorna 0.

      Não sei se os ) e ; estão no lugar, mas tenta dessa forma.


      Espero ter ajudado, abraço!

      Excluir
  8. Olá, Ricardo!

    Primeiramente, parabéns pelo post e pelo blog! Material de primeiríssima!

    Tenho um código que encontrei na internet que faz algo parecido com o seu, pode encontrá-lo facilmente pesquisando por PROCVCONCAT. Como o nome já diz, esse código concatena todos os valores encontrados pelo PROCV na mesma célula.

    Entretanto, quando trabalhamos com muitos registros, o tempo de processamento ao atualizar qualquer campo é muito grande!

    Dessa maneira, você acredita ser possível alterar a função que escreveu para que todos os valores encontrados sejam apresentados na mesma célula? Estou pensando aqui, mas ainda não descobri um jeito...

    Desde já agradeço e parabéns novamente!!

    Abraços!

    ResponderExcluir
    Respostas
    1. Ola Felipe, muito interessante o PROCVCONCAT eu ainda não a conhecia. Ja no nosso exemplo pensando aqui rapidamente, teria que criar um formula que concatenasse todos os resultados em um unica celula. Creio que a única forma é desenvolver um código especifico para tal usando linguagem de programação VBA.

      Abraços!

      Excluir
  9. Boa tare Ricardo,
    Estou tentando resolver um problema;
    Tenho uma tabela de preços e criei um campo para orçamento onde usando o PROCV o cliente irá colocar do produto ao lado da descrição do produto e em outro célula irá criando um suposto pedido. O problema é que quando um cod esta distante do outro no espaço que reservei para ir saindo o orçamento também acaba dando o mesmo espaço.
    Ex cod procv em um produto na célula 10 e um outro na célula 20 , no lugar do orçamento aparece 10 células vazias até achar o valor procurado.
    Gostaria que buscasse o valor logo em seguida para que ficasse o orçamento pronto.
    Em uma planinha com 1.000 produtos daria espaços gigantesco ficaria bem complicado.

    ResponderExcluir
    Respostas
    1. Olá pelo que entendi existe linhas em branco na planilha de cadastro de produtos. O ideal para que alcance o resultado esperado, na planilha de cadastro de produtos, cadastrar todos os códigos um em seguida do outro, sem linhas em branco.

      Excluir
  10. Olá Ricardo, muitíssimo obrigada por disponibilizar esse tipo de ajuda tão didática pra gente que as vezes precisa de uma função de última hora e não sabe como fazer. Estava quebrando cabeça para fazer um check list dinâmico de documentação para abertura de contas bancárias, utilizando esta formula 'ninja' e associando-a a outras fórmulas, consegui montá-lo sem quebrar muito a cabeça. Fui bastante elogiada pelos meus gestores e fiquei ainda mais apaixonada por tudo o que o excel é capaz de proporcionar para facilitar nosso dia-a-dia. Sua página está nos meus favoritos e também a divulguei para colegas que tem a vontade de aprender ou melhorar os conhecimentos nessa ferramenta. Mais uma vez, obrigada!!!

    ResponderExcluir
    Respostas
    1. Olá Pamela, muito obrigado pelas palavras, elas me motivam a continuar com este projeto. Fico muito feliz por você ter alcançado seu objetivo, obrigado por acompanhar e fica aqui o meu convite para conhecer meu canal do youtube: https://goo.gl/maJNtd

      Um grande abraço e sucesso para você!

      Excluir
  11. Muito obrigado pela ajuda, me salvou. Estou a 3 dias neste problema

    grande abraço.

    ResponderExcluir
  12. olá muito obrigado pela explicação.
    so me surgiu um unica duvida ( o que o - 5) representa nesta formula ?
    não consegui entender bem.


    desde já agradeço

    ResponderExcluir
    Respostas
    1. Ola, representa as cinco primeiras linhas que não fazem parte da lista de Nomes.

      Excluir
  13. Muito boa, Ricardo! Mas e no caso de ter que utilizar dois parâmetros? Por exemplo, se fosse a planilha de controle de um hipermercado com várias categorias de produtos e eu quisesse saber todas as vezes que o cliente "Marcos" comprou produtos da categoria "alimentos", e dizer quais foram esses alimentos? Seria possível?

    ResponderExcluir
    Respostas
    1. Ola Samuel, você precisa usar uma Função SE dentro SE para fazer um PROCV com duas condições retornar todos os valores. Na parte da busca acrescenta um segundo SE dentro do primeiro, exemplo:

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

      Um abraço!

      Excluir
  14. Muito bom mesmo! Explicação impecável. Parabéns!

    ResponderExcluir
  15. Boa noite Ricardo! Eu criei uma tela igual a sua do exemplo, copiei a fórmula e copiei em E6, só que o valor que apareceu foi Notebook 15,5"
    e na E7 em diante ficou em branco. Eu não estou conseguindo descobrir o que fiz de errado.

    ResponderExcluir
    Respostas
    1. Boa noite João! Por se tratar de uma fórmula matricial é necessário teclar CTRL+SHIFT+ENTER ao final da fórmula para que funcione corretamente, verifique se você fez este procedimento. Abraços!

      Excluir
    2. Agora consegui, eu estava teclando errado o CTRL+SHIFT+ENTER,
      AGORA FICOU PERFEITO... Muito obrigado e parabéns pelo site!

      Excluir
    3. Perfeito João, muito obrigado e continue nos acompanhando! Abraços!

      Excluir
  16. Ricardo. Muito obrigada por compartilhar seu conhecimento. Me ajudou muito mesmo.

    ResponderExcluir
    Respostas
    1. Maravilha Karol! Fico feliz em ter ajudado!

      Um grande abraço!

      Excluir
  17. Voce é o cara!
    Ajuda muito o que faz aqui...

    ResponderExcluir
    Respostas
    1. Opa Afegão! Obrigado pelo comentário! É muito bacana receber feedbacks como o seu, que nos incentivam bastante a produzir buscando cada vez mais excelência!

      Grande abraço! :)

      Excluir
  18. Eu queria fazer exatamente isso mas no lugar de Marcos ser uma data e na lstagem n ser apenas uma coisa, tipo o produto mas sim produto preco ets tem como? Agradeco!!

    ResponderExcluir
    Respostas
    1. Oi Morgana, não vejo problema em usar uma data como critério de busca. No entanto para gerar um relatório que retorne o produto, o preço e demais campos usando este exemplo, é necessário desenvolver uma fórmula para cada coluna que você deseja retornar. Abraços!

      Excluir
  19. Ricardo, boa tarde!
    Parabéns pelo trabalho. Muito bom! Simples, objetivo e didático.
    Estou quebrando a cabeça para buscar a solução para o seguinte problema:
    Tenho duas planilhas, ambas tendo como campo de referência NCM/SH. Uma planilha tem notas fiscais, em que este campo NCM/SH é preenchido com 8 caracteres, com o seguinte formato: XXXX.XX.XX; a outra planilha contém também o campo NCM/SH, mas que nem sempre o conteúdo é do mesmo tamanho, ou seja, as vezes a NCM/SH aparece com 8 caracteres (XXXX.XX.XX), ou com 7 (XXXX.XX.X), ou com 6 (XXXX.XX), ou com 5 (XXXX.X) ou, finalmente, somente com 4 (XXXX ou XX.XX).
    Como fazer a busca da NCM/SH da planilha 1 de modo a encontrar o correto enquadramento na planilha 2?
    Exemplo: tenho a NCM/SH 8529.90.20 na planilha 1; na planilha 2 tenho o registro de 3 NCM/SH: 8529.90.12 (na linha 183 da planilha 2 e que não me interessa); 8529.10.90 (na linha 184 da planilha 2 e que não interessa) e 8529 (na linha 1104 da planilha 2 que é a NCM geral e que me interessa).
    Conseguiu compreender?
    Agradeço desde já sua atenção.

    ResponderExcluir
    Respostas
    1. Boa noite João

      Acredito que o caminho a seguir é extrair os 4 primeiros caracteres da NCM e usar como parametro de busca na Função Procv.

      O primeiro passo é Extrair os 4 primeiro dígitos da NCM e multiplicar por 1 para transformar em um número inteiro. Para isso use a Função ESQUERDA do Excel.

      1*ESQUERDA(F5;4) onde F5 é a célula que contem a NCM no padrão XXXX.XX.XX

      Então use a Função PROCV para buscar este valor na sua Planilha que contem o cadastro das NCM:

      =PROCV(1*ESQUERDA(F5;4);I5:J7;2;0)

      Um forte abraço!

      Excluir