10 exemplos de PROCV para iniciantes e usuários avançados

A função PROCV é a referência.

Se você sabe como usar a função PROCV, sabe algo sobre o Excel.

Caso contrário, é melhor não listar o Excel como um de seus pontos fortes em seu currículo.

Fiz uma entrevista em grupo e uma vez que um candidato mencionou o Excel como sua área de atuação, a primeira coisa que foi perguntada foi - você entendeu - o recurso PROCV.

Agora que sabemos a importância desta função do Excel, faz sentido compreendê-la completamente para que possamos dizer com orgulho - "Eu sei uma coisa ou duas no Excel".

Este vai ser um grande tutorial VLOOKUP (pelos meus padrões).

Falarei sobre isso e depois mostrarei um exemplo útil e prático de PROCV.

Quando usar a função PROCV no Excel?

A função PROCV funciona melhor quando você procura pontos de dados correspondentes em uma coluna e, quando um ponto de dados correspondente é encontrado, você pega a coluna à direita da linha e obtém o valor do número especificado de células.

Vamos dar um exemplo simples aqui para entender quando usar o Vlookup no Excel.

Lembre-se, todo mundo costumava procurar freneticamente por seus nomes e notas quando as notas dos testes saíam e ficavam coladas em quadros de avisos (pelo menos era o que acontecia muito quando eu estava na escola).

Veja como funciona:

• Você vai até o quadro de avisos e começa a procurar seu nome ou número de registro (mova o dedo para cima e para baixo na lista).
• Depois de encontrar seu nome, você moverá os olhos para a direita do nome/número de registro para ver sua pontuação.

É exatamente isso que o recurso Excel VLOOKUP faz por você (sinta-se à vontade para usar este exemplo em sua próxima entrevista).

A função PROCV procura um valor especificado em uma coluna (no exemplo acima, é seu nome) e, quando encontra uma correspondência especificada, retorna um valor (o token obtido) na mesma linha.

sintaxe

= VLOOKUP (lookup_value, table_array, col_index_num, [range_lookup])

Parâmetros de entrada

• valor_pesquisa –Este é o valor de pesquisa que você está tentando localizar na coluna mais à esquerda da tabela.Pode ser um valor, uma referência de célula ou uma sequência de texto.No exemplo da planilha, este seria seu nome.
• tabela_array –Esta é a matriz de tabela na qual você deseja pesquisar o valor.Isso pode ser uma referência a um intervalo de células ou a um intervalo nomeado.No exemplo da tabela de pontuação, esta seria a tabela inteira contendo as pontuações de todos para cada assunto
• índice_coluna –Este é o número de índice da coluna do qual você deseja obter valores correspondentes.No exemplo da tabela de frações, se você quiser a fração da matemática (que é a primeira coluna da tabela que contém a fração), você pode olhar para a coluna 1.Se você quiser uma pontuação física, pode consultar as colunas 1 e 2.
• [Pesquisa de alcance] -Aqui você pode especificar se deseja uma correspondência exata ou aproximada.Se omitido, o padrão é TRUE - correspondência aproximada(ver notas adicionais abaixo).

Notas adicionais (chato, mas importante saber)

• As correspondências podem ser exatas (FALSE ou 0 em range_lookup) ou aproximadas (TRUE ou 1).
• Em uma pesquisa aproximada, certifique-se de que a lista esteja classificada em ordem crescente (de cima para baixo), caso contrário os resultados podem ser imprecisos.
• Quando range_lookup é TRUE (pesquisa aproximada) e os dados são classificados em ordem crescente:
  • Se a função PROCV não puder localizar o valor, ela retornará o maior valor menor que valor_procurado.
  • Retorna um erro #N/A se valor_procurado for menor que o valor mínimo.
  • Se lookup_value for texto, curingas podem ser usados ​​(veja o exemplo abaixo).

Agora, espero que você tenha um entendimento básico do que a função PROCV pode fazer, vamos descascar a cebola e ver alguns exemplos práticos da função PROCV.

10 Exemplos de PROCV do Excel (básico e avançado)

Aqui estão 10 exemplos úteis de como usar o Excel Vlookup para mostrar como usá-lo em seu trabalho diário.

Exemplo 1 - Encontre a pontuação matemática de Brad

No exemplo PROCV abaixo, tenho uma lista com nomes de alunos na coluna mais à esquerda e notas para diferentes disciplinas nas colunas B a E.

Agora vamos trabalhar e usar a função PROCV para fazer o que ela faz de melhor.A partir dos dados acima, preciso saber o que Brad marcou em matemática.

A partir dos dados acima, preciso saber o que Brad marcou em matemática.

Aqui está a fórmula PROCV que retorna a pontuação matemática de Brad:

=PROCV("Brad",$A$3:$E$10,2,0)

A fórmula acima tem quatro parâmetros:

• "Brad": - Este é o valor de pesquisa.
• $A$3:$E$10 - Este é o intervalo de células que estamos olhando.Lembre-se que o Excel procura valores de pesquisa na coluna mais à esquerda.Neste exemplo, ele procura o nome Brad em A3:A10, que é a coluna mais à esquerda da matriz especificada.
• 2 – Uma vez que a função encontra o nome de Brad, ela vai para a segunda coluna do array e retorna o valor na mesma linha que Brad.Um valor de 2 aqui significa que estamos procurando pontuações da segunda coluna da matriz especificada.
• 0 – Isso informa à função PROCV para procurar apenas correspondências exatas.

Veja como funciona a fórmula PROCV no exemplo acima.

Primeiro, ele procura o valor Brad na coluna mais à esquerda.Ele procura o valor na célula A6 de cima para baixo.

Depois de encontrar o valor, ele se move para a direita na segunda coluna e pega o valor nela.

Você pode usar a mesma construção de fórmula para obter a pontuação de qualquer pessoa em qualquer assunto.

Por exemplo, para encontrar a pontuação de Maria em Química, use a seguinte fórmula PROCV:

=PROCV("Maria",$A$3:$E$10,4,0)

No exemplo acima, o valor de pesquisa (nome do aluno) é inserido entre aspas duplas.Você também pode usar referências de célula que contêm valores de pesquisa.

A vantagem de usar referências de células é que torna a fórmula dinâmica.

Por exemplo, se você tiver uma célula com o nome de um aluno e estiver obtendo notas de matemática, ao alterar o nome do aluno, os resultados serão atualizados automaticamente (conforme mostrado abaixo):

Se você inserir um valor de pesquisa que não seja encontrado na coluna mais à esquerda, um erro #N/A será retornado.

Exemplo 2 - Pesquisa bidirecional

No Exemplo 1 acima, codificamos os valores da coluna.Portanto, a fórmula sempre retornará a pontuação de Math porque usamos 2 como o número de índice da coluna.

Mas e se você quiser tornar o valor PROCV e o número do índice da coluna dinâmicos.Por exemplo, conforme mostrado abaixo, você pode alterar o nome do aluno ou o nome da disciplina e a fórmula PROCV obterá a pontuação correta.Aqui está um exemplo de uma fórmula PROCV bidirecional.

Aqui está um exemplo de uma função PROCV bidirecional.

Para fazer essa fórmula de pesquisa bidirecional, você também precisa tornar a coluna dinâmica.Assim, quando o usuário muda de assunto, a fórmula seleciona automaticamente a coluna correta (2 para matemática, 3 para física, etc.).

Para fazer isso, você precisa usar a função MATCH como um parâmetro de coluna.

Aqui está a fórmula PROCV que fará isso:

=VLOOKUP(G4,$A$3:$E$10,MATCH(H3,$A$2:$E$2,0),0)

A fórmula acima usa MATCH(H3,$A$2:$E$2,0) como o número da coluna.A função CORRESP toma o nome do tópico como valor de pesquisa (em H3) e retorna sua posição em A2:E2.Portanto, se você usar Math, ele retornará 2 porque Math é encontrado em B2 (que é a segunda célula no intervalo de matriz especificado).

Exemplo 3 - Usando o menu suspenso como valor de pesquisa

No exemplo acima, temos que inserir os dados manualmente.Isso pode ser demorado e propenso a erros, especialmente se você tiver muitos valores de pesquisa.

Uma boa ideia nesse caso é criar uma lista suspensa de valores de pesquisa (neste caso pode ser nome e disciplina do aluno) e simplesmente selecionar na lista.

Dependendo da seleção, a fórmula atualizará automaticamente o resultado.

Como mostrado abaixo:

Esse é um ótimo componente de painel porque você pode ter um enorme conjunto de dados de centenas de alunos no back-end, mas os usuários finais (digamos, professores) podem simplesmente descer do .

Como eu faço isso:

A fórmula PROCV usada neste exemplo é a mesma usada no Exemplo 2.

=VLOOKUP(G4,$A$3:$E$10,MATCH(H3,$A$2:$E$2,0),0)

Os valores de pesquisa foram convertidos em listas suspensas.

Aqui estão as etapas para criar uma lista suspensa:

• Selecione a célula que precisa de uma lista suspensa.Neste exemplo, em G4, precisamos do nome do aluno.
• Vá para Dados -> Ferramentas de Dados -> Validação de Dados.
• Na guia Configurações da caixa de diálogo Validação de dados, selecione Lista na lista suspensa Permitir.
• Em Origem, selecione $A$3:$A$10
• Clique OK.

Agora você verá a lista suspensa na célula G4.Da mesma forma, você pode criar um em H3 para um tema.

Exemplo 4 - Pesquisa de três vias

O que é uma pesquisa de três vias?

No Exemplo 2, usamos uma tabela de pesquisa contendo pontuações de alunos em diferentes disciplinas.Este é um exemplo de pesquisa bidirecional porque usamos duas variáveis ​​para obter as pontuações (nome do aluno e nome da disciplina).

Agora, digamos que em um ano, um aluno fará três níveis diferentes de exames, exames de unidade, exames de meio de semestre e exames finais (este é o exame dos meus dias de estudante).

Uma pesquisa de três vias será capaz de obter a pontuação de um aluno para um assunto específico de um nível de exame específico.

Como mostrado abaixo:

No exemplo acima, a função PROCV pode pesquisar e retornar as notas dos alunos especificados nas disciplinas especificadas em três tabelas diferentes (testes unitários, intermediários e finais).

Aqui está a fórmula usada na célula H4:

=VLOOKUP(G4,CHOOSE(IF(H2="Unit Test",1,IF(H2="Midterm",2,3)),$A$3:$E$7,$A$11:$E$15,$A$19:$E$23),MATCH(H3,$A$2:$E$2,0),0)

Essa fórmula usa a função CHOOSE para garantir que a tabela correta seja referenciada.Vamos analisar a parte CHOOSE da fórmula:

CHOOSE(IF(H2=”Unit Test”,1,IF(H2=”Midterm”,2,3)),$A$3:$E$7,$A$11:$E$15,$A$19:$E$23)

O primeiro argumento para a fórmula é IF(H2=”Unit Test”,1,IF(H2=”Midterm”,2,3)), que examina a célula H2 e vê o nível de teste referenciado.Se for um teste de unidade, é retornado $A$3:$E$7, que contém a pontuação do teste de unidade.Retorna $A$11:$E$15 se for um exame de meio de período, caso contrário, retorna $A$19:$E$23.

Isso torna a matriz de tabela VLOOKUP dinâmica, tornando-a uma pesquisa de três vias.

Exemplo 5 - Obter o último valor de uma lista

Você pode criar uma fórmula PROCV para obter o último valor em uma lista.

O maior número positivo que você pode usar no Excel é 9.99999999999999 307 + . Isso também significa que o maior número de pesquisa no número PROCV é o mesmo.

Acho que você nunca precisará de cálculos envolvendo números tão grandes.Isso é exatamente o que podemos usar para obter o último número da lista.

Suponha que você tenha um conjunto de dados como o seguinte(em A1:A14), e você deseja obter o último número da lista.

Aqui está a fórmula que você pode usar:

=PROCV(9.99999999999999E+307,$A$1:$A$14,TRUE)

Observe que a fórmula acima usa um PROCV correspondente aproximado  (Observe o TRUE no final da fórmula, não FALSE ou 0).Além disso, observe que essa fórmula PROCV funciona sem classificar a lista.

Veja como funciona a função PROCV aproximada.Ele verifica a coluna mais à esquerda de cima para baixo.

• Esse valor é retornado se uma correspondência exata for encontrada.
• Se encontrar um valor maior que o valor de pesquisa, ele retornará o valor na célula acima dele.
• Se o valor de pesquisa for maior que todos os valores da lista, o último valor será retornado.

No exemplo acima, o terceiro caso está em ação.

devido a9.99999999999999 307 +é o maior número que pode ser usado no Excel, portanto, quando usado como valor de pesquisa, ele retorna o último número da lista.

Da mesma forma, você também pode usá-lo para retornar o último item de texto da lista.Aqui está a fórmula que pode fazê-lo:

=PROCV("zzz",$A$1:$A$8,1, VERDADEIRO )

Siga a mesma lógica.O Excel examina todos os nomes e, como zzz é considerado maior do que qualquer nome/texto que comece com uma letra antes de zzz, ele retornará o último item da lista.

Exemplo 6 - Pesquisa parcial usando curingas e PROCV

Os curingas do Excel são úteis em muitas situações.

É esta poção mágica que dá superpoderes à sua receita.

Uma pesquisa parcial é necessária quando você precisa pesquisar um valor em uma lista e não há correspondência exata.

Por exemplo, suponha que você tenha um conjunto de dados como o seguinte e queira localizar a empresa ABC em uma lista, mas a lista tem ABC Ltd em vez de ABC.

Você não pode usar ABC como o valor de pesquisa porque não há correspondência exata na coluna A.Correspondências aproximadas também podem levar a resultados incorretos, e a lista precisa ser classificada em ordem crescente.

No entanto, você pode usar curingas na função PROCV para obter correspondências.

Insira a seguinte fórmula na célula D2 e ​​arraste-a para outras células:

=PROCV("*"&C2&"*",$A$2:$A$8,1,FALSO)

Como funciona essa fórmula?

Na fórmula acima, em vez de usar o valor de pesquisa como está, ele é cercado por asteriscos curinga (*) - "*"&C2&"*"

Asteriscos são caracteres curinga no Excel que podem representar qualquer número de caracteres.

Use asteriscos ao redor do valor de pesquisa para informar ao Excel que ele precisa encontrar qualquer texto que contenha a palavra em C2.Pode ter qualquer número de caracteres antes ou depois do texto em C2.

Por exemplo, a célula C2 contém ABC, portanto, a função PROCV examina os nomes em A2:A8 e procura ABC.Ele encontra uma correspondência na célula A2 porque contém ABC da ABC Ltd. Não importa se há caracteres à esquerda ou à direita de ABC.Será considerado uma correspondência até que haja ABC na string de texto.

Nota: A função PROCV sempre retorna o primeiro valor correspondente e interrompe outras pesquisas.Então, se houver ABC na lista Ltd.e ABC Corporation, ele retornará o primeiro e ignorará o restante.

Exemplo 7 - VLOOKUP retorna erro apesar da correspondência de valor de pesquisa

Você vai ficar louco quando você ver que há um valor de pesquisa correspondente e a função PROCV retornar um erro.

Por exemplo, no caso abaixo, há uma correspondência (Matt), mas a função PROCV ainda retorna um erro.

Agora, enquanto podemos ver que há uma correspondência, o que não podemos ver a olho nu é que pode haver espaços à esquerda ou à direita.Se você tiver esses espaços extras antes, depois ou entre o valor de pesquisa, não é uma correspondência exata.

Isso geralmente acontece quando você importa dados de um banco de dados ou obtém dados de outra pessoa.Esses espaços à frente/à direita tendem a se infiltrar.

A solução aqui é a função TRIM.Ele remove quaisquer espaços à esquerda ou à direita ou espaços extras entre as palavras.

Aqui está a fórmula que lhe dará o resultado correto.

=PROCV("Matt",TRIM($A$2:$A$9),1,0)

Como esta é uma fórmula de matriz, use Control+Shift+Enter em vez de Enter.

Outra abordagem pode ser primeiro processar sua matriz de pesquisa com a função TRIM para garantir que todos os espaços extras tenham desaparecido e, em seguida, usar a função PROCV como de costume.

Exemplo 8 - Fazendo uma pesquisa com distinção entre maiúsculas e minúsculas

Por padrão, os valores de pesquisa na função PROCV não diferenciam maiúsculas de minúsculas.Por exemplo, se seu valor de pesquisa for MATT, matt ou Matt, é a mesma coisa para a função VLOOKUP.Ele retorna o primeiro valor correspondente, independentemente de maiúsculas e minúsculas.

Mas se você quiser uma pesquisa com distinção entre maiúsculas e minúsculas, precisará usar a função EXACT e a função VLOOKUP.

Isto é um exemplo:

Como você pode ver, as três células têm o mesmo nome (em A2, A4 e A5), mas com letras maiúsculas diferentes.À direita, temos três nomes (Matt, MATT e matt) e suas pontuações em matemática.

A função VLOOKUP não está atualmente equipada para lidar com valores de pesquisa que diferenciam maiúsculas de minúsculas.No exemplo acima, sempre retorna 38, que é a pontuação de Matt em A2.

Para fazer distinção entre maiúsculas e minúsculas, precisamos usar uma coluna auxiliar (como mostrado abaixo):

Para obter o valor na coluna de ajuda, use a função =ROW().Ele só obterá o número da linha na célula.

Depois de ter a coluna auxiliar, aqui está a fórmula que fornece um resultado de pesquisa que diferencia maiúsculas de minúsculas.

=VLOOKUP(MAX(EXACT(E2,$A$2:$A$9)*(ROW($A$2:$A$9))),$B$2:$C$9,2,0)

Agora vamos decompô-lo e entender o que ele faz:

• EXACT(E2,$A$2:$A$9) – Esta parte compara o valor de pesquisa em E2 com todos os valores em A2:A9.Ele retorna um array de TRUE/FALSE, onde TRUE é retornado no caso de uma correspondência exata.Neste caso retornará o seguinte array: {TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}.
• EXACT(E2,$A$2:$A$9)*(ROW($A$2:$A$9) - esta parte multiplica o array TRUE/FALSE pelo número da linha. Enquanto houver TRUE, ele dará o número da linha , caso contrário dá 0. Neste caso, retorna {2;0;0;0;0;0;0;0}.
• MAX(EXACT(E2,$A$2:$A$9)*(ROW($A$2:$A$9))) – 這部分返回數字數組中的最大值。Nesse caso, ele retornará 2 (que é o número exato da linha).
• Agora usamos esse número como valor de pesquisa e usamos a matriz de pesquisa como B2:C9

Nota: Como esta é uma fórmula de matriz, use Control + Shift + Enter em vez de apenas digitar.

Exemplo 9 - Usando VLOOKUP com várias condições

A função PROCV do Excel, em sua forma básica, procura um valor de pesquisa e retorna o valor correspondente de uma linha especificada.

Mas geralmente é necessário PROCV no Excel com vários critérios.

Suponha que você tenha dados contendo nomes de alunos, tipos de teste e pontuações de matemática (mostrados abaixo):

Usar a função PROCV para obter a pontuação de matemática de cada aluno em seu respectivo nível de teste pode ser um desafio.

Por exemplo, se você tentar usar VLOOKUP com Matt como o valor de pesquisa, ele sempre retornará 91, a pontuação da primeira ocorrência de Matt na lista.Para obter a pontuação Matt para cada tipo de exame (unidade, meio do semestre e final), você precisa criar um valor de pesquisa exclusivo.

Isso pode ser feito usando colunas auxiliares.O primeiro passo é inserir uma coluna auxiliar à esquerda da fração.

Agora, para criar um qualificador exclusivo para cada instância de um nome, use a seguinte fórmula em C2: =A2&”|”&B2

Copie esta fórmula para todas as células na coluna auxiliar.Isso criará um valor de pesquisa exclusivo para cada instância do nome (como mostrado abaixo):

Agora, embora haja nomes duplicados, quando os nomes são combinados com níveis de exame, não há duplicatas.

Isso é fácil porque agora você pode usar o valor da coluna auxiliar como o valor de pesquisa.

Esta é a fórmula que lhe dá o resultado em G3:I8.

=PROCV($F3&"|"&G$2,$C$2:$D$19,2,0)

Aqui combinamos o nome do aluno e o nível do exame para obter o valor de pesquisa, usamos esse valor de pesquisa para verificar na coluna auxiliar os registros correspondentes.

Nota: No exemplo acima, usamos | Usado como delimitador ao adicionar texto a colunas auxiliares.Em alguns casos muito raros (mas possíveis), você pode ter dois critérios diferentes, mas combiná-los acabará dando o mesmo resultado.Isto é um exemplo:

Observe que enquanto A2 e A3 são diferentes e B2 e B3 são diferentes, a combinação é basicamente a mesma.No entanto, se você usar delimitadores, até mesmo a combinação será diferente (D2 e D3).

Aqui está um tutorial sobre como usar VLOOKUP com várias condições sem usar colunas auxiliares.

Exemplo 10 - Tratamento de erros ao usar a função PROCV

A função PROCV do Excel retorna um erro quando o valor de pesquisa especificado não pode ser encontrado.Se o VLOOKUP não encontrar um valor, você provavelmente não quer que valores de erro feios interfiram na estética dos seus dados.

Você pode remover facilmente valores de erro com texto completo de qualquer significado, como "Não disponível" ou "Não encontrado".

Por exemplo, no exemplo abaixo, quando você tenta encontrar a pontuação de Brad na lista, ele retorna um erro porque o nome de Brad não está na lista.

Para remover esse erro e substituí-lo por algo significativo, envolva a função PROCV em uma função SEERRO.

Aqui está a fórmula:

=SEERRO(PROCV(D2,$A$2:$B$7,2,0),"Não encontrado")

A função SEERRO verifica se o valor retornado pelo primeiro argumento (neste caso, a função PROCV) é um erro.Se não for um erro, retorne o valor através da função PROCV, caso contrário, retorne Não encontrado.

A função SEERRO está disponível desde o Excel 2007.Se você estiver usando uma versão anterior, use as seguintes funções:

=IF(ISERROR(VLOOKUP(D2,$A$2:$B$7,2,0)),"Not Found",VLOOKUP(D2,$A$2:$B$7,2,0))

Isso é tudo para este tutorial PROCV.

Estou tentando apresentar o principal exemplo de uso da função Vlookup no Excel.Deixe-me saber na seção de comentários se você gostaria de ver mais exemplos adicionados a esta lista.

Funções do Excel relacionadas:

• Função HLOOKUP do Excel.
• Função XLOOKUP do Excel
• Função de índice do Excel.
• Função indireta do Excel.
• Função de correspondência do Excel.
• Função de deslocamento do Excel.