Variações da função PROCV no Excel: mais de uma condição e valores repetidos

Sumário

O PROCV é uma das melhores funções de busca que o Excel disponibiliza para seus usuários. Digo isso, porque não é muito complicado de entender o seu funcionamento e existem diversos tutoriais na internet de como usar a função. Nós mesmo já escrevemos um post bem completo sobre como usar PROCV (VLOOKUP).

Curso de Excel - Formatação Condicional

Se você é um dos que preferem ver a explicação em vídeo, temos um módulo sobre a função PROCV no nosso curso básico de excel online. Caso você já conheça o PROCV e queira conhecer algumas funções mais avançadas como a ÍNDICE (que falaremos mais ao longo desse post), recomendo o nosso curso de Excel Avançado.

2 Possíveis Problemas com o PROCV

Existem dois problemas que podem dificultar o uso dessa funcionalidade:

  • 1. Quando você tem mais de uma condição

PROCV com mais de uma condição

No exemplo acima, temos duas condições, marca e tipo de veículo. Se quiséssemos utilizar o PROCV sem nenhuma outra função para ajudar a achar o valor base na tabela de especificação de valor teríamos problemas, pois ou utilizaríamos a coluna de marca ou a de tipo de veículo.

  • 2. Quando você quer ver todos os resultados possíveis

PROCV retornando todos os valores

Nesse caso, temos um problema clássico, que é quando você quer ver todos os valores de vendas realizadas, mas a PROCV só iria te retornar um valor e, nesse caso, temos um mínimo de 2 valores por opção.

Vamos ver agora como resolver cada um desses problemas e o que você precisa usar para chegar no melhor resultado.

Curso de Excel - Formatação Condicional

1. Exemplo de PROCV com mais de uma condição

Como tudo no Excel, é possível sempre pensar em lógicas utilizando outras funções para resolver o seu problema. Por isso, vamos primeiro entender o problema que estamos enfrentando aqui entendendo o conceito da PROCV. Veja sua sintaxe:

=PROCV(valor_procurado;matriz_tabela;núm_índice_coluna;intervalo)

  • O problema:

O valor procurado é um valor único e no nosso caso queremos olhar dois valores diferentes: marca e tipo de veículo

PROCV com mais de uma condição - valor procurado

Não conseguiríamos utilizar no valor_procurado as células G4 e G5 ao mesmo tempo.

  • A solução:

Encontrar uma função que transforme esses 2 valores em um valor único. Como sabemos, a função CONCATENAR serve para agrupar cadeias de caracteres. Se quiser se lembrar mais, veja o nosso post com 5 aplicações da CONCATENAR. Ao utilizar ela nas células G4 e G5, teríamos algo como:

PROCV com mais de uma condição - valor procurado com concatenar

Veja que eu coloquei um hífen na concatenação para ter um texto mais amigável. Sem ele ficaria IvecoFurgão, o que poderia confundir um usuário que não entendesse o objetivo da mesma.

Curso de Excel - Formatação Condicional

  • Como ficaria o resultado:

Agora, para ter o resultado corretamente, você precisa criar uma coluna a mais na tabela de base de dados, para ter o mesmo tipo de resultado. Veja:

PROCV com mais de uma condição - tabela modificada

Se a coluna extra não te agradar, você pode ocultar sem problema algum. Agora fica só faltando usar o PROCV utilizando como valor procurado a célula que adicionamos e a matriz que busca os valores nas colunas D e E, veja na fórmula:

PROCV com mais de uma condição - PROCV com CONCATENAR

Pronto, a função funciona perfeitamente e você pode utilizar até para mais de duas colunas se quiser. Também é possível usar o CONCATENAR diretamente na função, veja:

PROCV com mais de uma condição - PROCV com CONCATENAR 2

2. Exemplo de PROCV em uma lista com valores repetidos

No nosso segundo caso não temos mais o problema de buscar 2 tipos de valores diferentes, mas temos uma única lista com valores repetidos:

PROCV retornando todos os valores - lista com valores repetidos

Veja que na nossa lista de vendedores temos o Jarbas (2 repetições), o Josias (4 repetições) e o Jonas (3 repetições). A tentar buscar um histórica das vendas de cada um deles vamos ter problemas.

Curso de Excel - Formatação Condicional

  • O problema:

Observe que ao utilizar o PROCV ele sempre vai buscar o primeiro valor_procurado Jarbas e, quando encontrar, vai retornar a segunda ou terceira coluna, dependendo do que estivermos buscando. Acontece que quando levamos essa fórmula para baixo para tentar ver as outras vendas ela não funciona, continua retornando os mesmos valores.

PROCV retornando todos os valores - erro no procv comum

  • A solução:

Podemos utilizar a função ÍNDICE em conjunto com outras (MENOR, SE e LIN) para criar uma função matricial que busque todas as ocorrências para o valor procurado.

PROCV retornando todos os valores - exemplo

  • Como ficaria o resultado:

Para ter o resultado corretamente será necessário usar a seguinte função:

{=SEERRO(ÍNDICE($B$4:$D$12;MENOR(SE($B$4:$B$12=$G$4;LIN($B$4:$B$12)-3);LIN(A1));2);””)}

Veja que a função tem chaves { } antes de começar e ao final, isso indica que é uma função matricial. Para tornar a sua função em uma função matricial basta apertar CTRL + SHIFT + ENTER depois de escrever ela por completo. Se modificássemos o nome do vendedor na nossa lista teríamos um novo resultado:

PROCV retornando todos os valores - resultado final

Passo a passo de uso das funções nessa fórmula matricial:

Como temos uma função bem complexa, vou te mostrar o passo a passo para que você entenda cada fator explicado.

  • Passo 1 – SE com LIN

Para começar, a primeira função que utilizaremos é a SE com LIN

=SE($B$4:$B$12=$G$4;LIN($B$4:$B$12)-3)

Basicamente eu estou pedindo para o Excel analisar a linha B entre as células 4 e 12 (que é onde eu tenho as vendas – poderia ser maior se eu tivesse mais vendas). Toda vez que ele encontrar um resultado igual ao valor de G4 (nome do vendedor), ele vai retornar a linha desse resultado.

No nosso caso acima seriam as linhas 5, 8, 11 e 12. Agora eu subtraio 3 desses valores para eu simular que minha lista comece na linha. Assim, eu teria os valores 2, 5, 8 e 9.

Curso de Excel - Formatação Condicional

  • Passo 2 – MENOR

Agora eu quero dizer ao Excel que dos valores que ele encontrou, que ele me retorne o menor

=MENOR(SE($B$4:$B$12=$G$4;LIN($B$4:$B$12)-3);LIN(A1))

PROCV retornando todos os valores - funções 1

Veja que a única parte da fórmula com referência relativa é a LIN(A1). Isso ocorre propositalmente para quando arrastarmos a fórmula para baixo, ela retornar o segundo, terceiro e quarto menor respectivamente. Por isso conseguimos colocar a ordem das vendas corretamente. Se marcássemos outro vendedor, teríamos apenas as vendas realizadas por ele numeradas na ordem correta:

PROCV retornando todos os valores - funções 2

Como o Jonas só realizou 3 vendas, aparece um erro #NÚM! no local da venda 4. De toda forma, a gente ainda não conseguiu colocar o nome do produto vendido, apenas a sua referência. Para chegar nesse resultado utilizaremos a função ÍNDICE…

  • Passo 3 – ÍNDICE

Como falamos lá no começo, essa é a função principal para chegar nesse resultado, já que ela faz buscas em uma determinada lista a partir de valores imputados. Dessa forma, faremos uma busca na tabela B4:D12 procurando a referência de linha obtida pela função menor e analisando sempre o valor da segunda coluna (tipo de veículo) da nossa tabela inicial de referência:

PROCV retornando todos os valores - funções 3

Assim transformamos os números em valores e já estamos com o resultado desejado. Agora só falta ajustar a fórmula para não aparecer erros e depois replicar para a coluna ao lado para obter os valores das vendas.

Curso de Excel - Formatação Condicional

  • Passo 4 – SEERRO

Esse passo é bem simples e sem mistério. Basta repetir a função usada colocando o SEERRO na frente e deixando um ” ” (valor em branco) no final. Lembrando que a função é matricial, desde o início, isso faz com que toda vez que mexermos na caixa de fórmula tenhamos que usar o CTRL + SHIFT + ENTER.

Lembre-se também de usar a alça de preenchimento para arrastar as fórmulas para baixo e não ter que fazer uma a uma.

PROCV retornando todos os valores - funções 4

Assim sumiremos com o valor de erro. Entenda, ele ainda está lá, só que fica disfarçado de valor em branco para sua planilha ficar mais amigável para quem estiver visualizando.

  • Passo 5 – Replicar a fórmula para buscar os valores base

Por fim, se quisermos fazer a mesma função na coluna ao lado é só copiar e colar. Obviamente que teremos que fazer uma mudança: trocar o valor 2 da função ÍNDICE para o valor 3. Faremos isso, porque agora queremos buscar os valores base (coluna D) da nossa tabela auxiliar.

PROCV retornando todos os valores - funções 5

Pronto, essa seria a função da coluna D:

{=SEERRO(ÍNDICE($B$4:$D$12;MENOR(SE($B$4:$B$12=$G$4;LIN($B$4:$B$12)-3);LIN(A1));2);””)}

Como você faz?

Se você curtiu as possibilidades do PROCV e da função ÍNDICE e quer aprender um pouco mais sobre elas, experimente conhecer o nosso curso de Excel Avançado.

Caso você utilize outras funções ou formas para chegar nesses resultados, conta pra gente nos comentários.

Curso de Excel - Formatação CondicionalEstá com dúvidas? Entre no Fórum de Excel da LUZ e envie a sua pergunta!

ARTIGOS MAIS RECENTES