Introdução à função VLOOKUP
A função VLOOKUP do Excel é sem dúvida, a função mais utilizada e falada. Em qualquer entrevista de emprego que requeira habilidades em Excel, esta é a pergunta mais feita se você souber usar a função PROCV.
A função PROCV está na categoria de pesquisa de funções do Excel. O V em VLOOKUP significa vertical. A função é usada para pesquisar dados estruturados verticalmente. Para pesquisa horizontal, existe uma função chamada HLOOKUP.
A função VLOOKUP do Excel procura a primeira correspondência de um determinado valor denominado valor de pesquisa, em uma tabela de dados e retorna o valor do índice de coluna fornecido. A correspondência pode ser aproximada ou exata.
Embora VLOOKUP seja fácil de usar, ele tem suas próprias limitações e cenários. Discutiremos tudo, incluindo os pontos fortes e fracos da função.
Sintaxe:
= VLOOKUP (lookup_value, table_array, col_index_number, [range_lookup])
O que é valor de pesquisa
Valor_procurado: É o valor que você deseja procurar em uma matriz de tabela.
Por exemplo, se você estiver procurando por um número de rolo. 110 na mesa do aluno, então é 110 é o valor de pesquisa.
Table_array: A tabela na qual você deseja procurar o valor de pesquisa.
Por exemplo, se você estiver procurando 110 na matriz da tabela B5: E17. Então B5: E17 é a matriz da sua tabela.
col_index_number: O número da coluna em Table Array da qual você deseja buscar resultados.
Por exemplo, se na matriz da tabela B5: E17 você deseja obter valor da coluna D então seu índice de coluna será 3 (contando de B a D (B, C, D)).
[Pesquisa de alcance]: FALSE se quiser pesquisar o valor exato, TRUE se quiser uma correspondência aproximada.
Como usar a função VLOOKUP
Quando você tem dados estruturados e deseja pesquisar dados nesses dados, a função VLOOKUP é a solução.
Modos de correspondência VLOOKUP
VLOOKUP tem dois modos de correspondência, correspondência aproximada e correspondência exata. Por padrão, VLOOKUP executa uma correspondência aproximada. Mas se você quiser forçar a função VLOOKUP a fazer uma correspondência exata, você também pode fazer isso. Na maioria das vezes, eu e eu acreditamos fortemente nos outros também, tentamos fazer uma correspondência exata usando a função PROCV. Não entendo por que os desenvolvedores do Excel pensam que os usuários gostariam de usar a correspondência aproximada principalmente.
1. VLOOKUP Correspondência Aproximada
= VLOOKUP (lookup_value, table_array, col_index_number, 1)
Na correspondência aproximada PROCV, se um valor não for encontrado, o o último valor menor que o valor de pesquisa é correspondido e o valor do índice de coluna fornecido é retornado.
VLOOKUP por padrão corresponde a uma correspondência aproximada, se omitirmos a variável de pesquisa de intervalo. Uma correspondência aproximada é útil quando você deseja fazer uma correspondência aproximada e quando você tem a matriz da tabela classificada em ordem crescente.
Vlookup procura o valor e se não encontrar o valor na matriz da tabela, então ele corresponde ao valor que é menor que o valor na matriz da tabela. Vamos entender isso por um exemplo.
Exemplo 1
Temos uma lista de alunos aqui. Cada aluno obteve algumas notas em um teste. Agora quero dar nota de acordo com suas notas.
Qualquer aluno que pontuou menos de 40 deve ser marcado com F, o estudante que pontuou entre 40 e 60 deve ser marcado com C, e assim por diante, conforme mostrado na imagem abaixo.
Escreveríamos esta fórmula VLOOKUP em E2 e a arrastaríamos para baixo.
= PROCV (D2, $ H $ 2: $ I $ 6,2, VERDADEIRO)
Como funciona?
No exemplo acima, nossos valores de pesquisa estão na coluna D, começando em D2. A tabela Array é H2: I6 (observe que ela é classificada em ordem crescente e absoluta). A coluna da qual estamos obtendo os dados é 2. E o tipo de correspondência que definimos aproximado passando TRUE (nada e 1 significará o mesmo).
Portanto, vamos examinar o primeiro valor de pesquisa D2 que contém 40.
- PROCV procura por 40 na primeira coluna (H) da matriz da tabela H2: I6.
- Ele encontra 40 na segunda posição na célula H3.
- Agora ele se move para a segunda coluna de H3 para I3 e retorna D.
Neste caso, o vlookup encontrou a correspondência exata. Vamos ver o próximo caso.
A segunda pesquisa valoriza D3 que contém 36.
- VLOOKUP procura 36 na primeira coluna da matriz da tabela H2: I6.
- PROCV não encontrou 36 em nenhum lugar na primeira coluna.
- Uma vez que PROCV não conseguiu encontrar 36, ele corresponde ao último valor encontrado que é menor que o valor de pesquisa.
- O primeiro valor menor que 36 é 0, portanto, ele retorna F que está relacionado a 0.
Isso acontece para cada caso aqui. Para 92, o último valor menor que 92 é 90. Portanto, A é retornado para correspondência aproximada.
2. VLOOKUP correspondência exata
Esta forma mais usada de PROCV e pode ser muito útil também. Quando você deseja procurar o valor exato na primeira coluna da matriz da tabela, você usa a correspondência exata. Por exemplo, se você estiver fazendo uma pesquisa de um ID, então gostaria de obter uma correspondência exata em vez de qualquer valor inferior a esse valor.
Para forçar VLOOKUP a uma correspondência exata no Excel, passamos 0 ou FALSE como parâmetro range_lookup.
= VLOOKUP (lookup_value, table_array, col_index_number, 0)
Se o valor não for encontrado na primeira coluna da matriz da tabela, a fórmula retornará o erro # N / A.
Vamos ver um exemplo.
Exemplo 2
Neste exemplo, eu quero escrever apenas o número do rolo de aluno em A2 e em B2 eu quero buscar o nome do estudante e em C2 seu Grau. Simples. A matriz da tabela é B5: D17. Porque?
Então, para fazer isso, escreva essas duas fórmulas na célula da célula B2 e C2, respectivamente.
= PROCV (A2, $ B $ 5: $ E $ 17,2,0)
= PROCV (A2, $ B $ 5: $ E $ 17,3,0)
Agora, qualquer que seja o ID que você escrever na célula A2, a função VLOOKUP procurará uma correspondência exata na coluna B e retornará o valor de col_index fornecido.
Como funciona a correspondência exata VLOOKUP?
A correspondência exata VLOOKUP no Excel é simples. Ele procura o valor de pesquisa na primeira coluna de table_array fornecido e retorna o valor do valor do número column_index fornecido. Se o valor não for encontrado, VLOOKUP retornará o erro # N / A.
Melhores usos da função VLOOKUP
O Vlookup tem muitos usos. Vou discutir alguns casos de uso mais úteis.
3. Use VLOOKUP do Excel para avaliar em vez da função IF aninhada complexa
Use a correspondência aproximada PROCV para substituir a função IF aninhada. No Exemplo 1, vimos como podemos usar vlookup para classificar os alunos de acordo com suas notas.
A correspondência aproximada VLOOKUP é mais rápida do que aninhada, visto que usa a pesquisa binária internamente.
4. Use PROCV para buscar dados
Esta tarefa mais comum pode ser realizada facilmente usando a função PROCV no Excel. No exemplo 2, fizemos isso para buscar os dados dos alunos usando o número do rolo.
5. Verifique se um valor está em um registro usando o Vlookup.
O VLOOKUP pode ser usado para verificar se um valor está em alguma lista ou não.
No Exemplo 2, quando escrevemos 152, ele retorna o erro # N / A. Se PROCV retornar # N / A, significa que não encontrou o valor fornecido em nenhum lugar dos dados fornecidos.
Eu preparei um breve artigo sobre isso. Você pode lê-lo aqui.
Usando uma fórmula VLOOKUP para verificar se existe um valor
Obtendo Índice de Coluna Automaticamente
Eu pertenço a esse grupo de pessoas que não querem repetir a mesma tarefa novamente e novamente. Portanto, mudar o número col_index repetidamente me irrita e sempre evito isso. Na verdade, evito qualquer tipo de edição em minhas fórmulas uma vez que as tenha escrito, a menos e até que não haja outra opção.
O índice da coluna pode ser calculado automaticamente no Excel. Há várias maneiras para se fazer isso. Vamos ver alguns deles aqui.
6. Use a função VLOOKUP com a função MATCH
Então, como sabemos, a função MATCH retorna o índice encontrado do valor combinado.
Portanto, se tivermos exatamente os mesmos títulos no local de pesquisa que os dados de origem, podemos usar para obter col_index. Como? Deixe-nos ver…
Nos dados abaixo, queremos apenas escrever o número do rolo no G2 e buscar o nome, as notas e a nota do Aluno em H2, qualquer que seja o título que escrevermos lá.
Escreva esta fórmula na célula H2
= PROCV (G2, B2: E14, CORRESPONDÊNCIA (H1, B1: E1,0), 0)
Agora, sempre que você alterar o Título em H1, esse valor em H2 será mostrado a partir dessa coluna.
Você pode usar um menu suspenso usando a validação de dados que tem todos os títulos da tabela, para torná-lo mais amigável.
Aqui VLOOKUP está fazendo uma correspondência exata. Agora VLOOKUP está fazendo seu trabalho usual de extração de dados. A mágica é feita pela função MATCH na posição col_index.
Aqui, VLOOKUP vê qualquer valor em G2 na primeira coluna da matriz da tabela B2: E14. Agora no local de col_index temos CORRESPONDÊNCIA (H1, B1: E1,0).
A função MATCH procura por qualquer valor em H1 no intervalo B1: E1 e retorna o índice do valor de correspondência.
Por exemplo, quando digitamos aluna em H1 procure dentro do alcance B1: E1. Ele é encontrado em C2. portanto, ele retorna 2. Se digitarmos Grau ele retornará a nota do aluno.
Observe que quando digitamos Region, ele retorna # N / A. Porque está fora do escopo da tabela. Falaremos sobre isso posteriormente no artigo.
Esta é a melhor maneira de tornar a PROCV automática. Existem outros métodos, mas eles não são tão flexíveis do que este.
7. Use a função VLOOKUP com a função COLUMN
A função de coluna retorna o número da coluna da referência fornecida. E o que precisamos para buscar dados usando VLOOKUP? Col_index. Portanto, usando a função COLUMN, podemos, é claro, obter dados de qualquer conjunto de dados.
Vejamos o exemplo acima. Mas agora precisamos recuperar dados inteiros. Não apenas uma coluna.
Escreva esta Fórmula em H2 e copie em I2 e J2.
= PROCV ($ G2, $ B $ 2: $ E $ 14, COLUNA (B1), 0)
Isso lhe dará um resultado dinâmico. Como? Vamos ver.
O VLOOKUP funciona normalmente. Em column_index nós escrevemos COLUNA (B1), que será traduzido para 2, uma vez que temos o nome do aluno na coluna 2 da coluna B, ele retorna o nome do aluno.
Importante: Isso funciona porque temos nossa tabela começando na coluna B. Se sua mesa está no meio da planilha, você precisará subtrair ou adicionar alguns números para que corresponda às suas necessidades.
Por exemplo, se a tabela acima estivesse começando em C1 na mesma estrutura, todas as fórmulas abaixo funcionariam bem.
= PROCV ($ G2, $ C $ 2: $ F $ 14, COLUNA (B1), 0)
= PROCV ($ G2, $ C $ 2: $ F $ 14, COLUNA (C1) -1,0)
= PROCV ($ G2, $ C $ 2: $ F $ 14, COLUNA (A1) +1,0)
8. PROCV para mesclar dados
Na maioria dos bancos de dados, há uma consulta que se mescla a tabelas, principalmente chamada de consulta de junção, mas o MS Excel não está em nenhum banco de dados, portanto, não tem consulta de junção. Mas isso não significa que não podemos unir duas tabelas se tivermos algumas colunas comuns em duas tabelas.
Portanto, quando você obtém alguns dados de dois departamentos da mesma empresa, deseja mesclá-los para analisar esses dados.
Por exemplo, ao obter notas de alunos de professores e, em seguida, registro de frequência do departamento administrativo, você gostaria de ver qual aluno obteve qual nota e qual é sua porcentagem de frequência, em um só lugar.
Precisamos mesclá-los na tabela abaixo.
Marcas, precisamos obter da Tabela de Marcas e de Presença dos Dados de Presença.
Escreva abaixo as Fórmulas na Célula D19 para obter Marcas e arrastar para baixo.
= PROCV (B19, $ B $ 2: $ D $ 15,3,0)
Escreva abaixo da fórmula na célula E19
= PROCV (B19, $ G $ 3: $ I $ 15,3,0)
E nós mesclamos duas tabelas em uma tabela. Você pode adicionar mais de duas tabelas, é claro. Você só precisa obter dados de todas as tabelas que deseja em um lugar usando PROCV.
Melhores práticas de VLOOKUP
Assim, em todos os exemplos acima, usamos apenas vlookup com dados brutos. Em todos os exemplos acima, tomamos cuidado com os dados que estávamos usando. Precisávamos ter cuidado ao fornecer referências de matrizes de tabelas. Mas existem certas maneiras que podem reduzir esse esforço extensivamente.
9. Use referências absolutas com VLOOKUP
Você deve ter notado que em alguns artigos usei intervalos absolutos (intervalo $ sinal). Bem, fúrias absolutas são usadas quando não queremos que o intervalo seja alterado enquanto copiamos a fórmula da célula para outras células.
Por exemplo, se eu tiver = VLOOKUP (G2,B2: E14, 2,0), 0) na célula H2, e se eu copiar ou arrastar para baixo na célula H3, a fórmula mudará para = VLOOKUP (G3,B3: E15, 2,0). Todas as referências são alteradas relativamente. Aqui, podemos querer alterar a referência do valor de pesquisa, mas não a matriz da tabela. Porque B2 sai do alcance e podemos querer tê-lo sempre em nossas matrizes de tabela.
Então, para evitar isso, usamos referências absolutas. Tornamos esses intervalos absolutos que não queremos alterar, como table_array em H2, = VLOOKUP (G2,$ B $ 2: $ E $ 14, 2,0), 0). Quando você copia H2 em H3, a fórmula será = VLOOKUP (G3,$ B $ 2: $ E $ 14, 2,0), 0). Observe que o valor de pesquisa é alterado, mas não table_array.
10. Use intervalos nomeados com VLOOKUP
No exemplo acima, usamos referência absoluta para corrigir o table_array. Parece bastante confuso e difícil de ler. Que tal se você pudesse apenas escrever 'dados' no lugar de $ B $ 2: $ E $ 14 e faria referência a $ B $ 2: $ E $ 14 sempre. Isso tornará ainda mais fácil ler e escrever a fórmula.
Portanto, basta selecionar o intervalo e nomeá-lo como "dados".
Para nomear um intervalo, selecione o intervalo. Clique com o botão direito, clique em Definir nome e nomeie o intervalo desejado. Agora apenas = VLOOKUP (G3,dados,2,0), 0) fórmula funcionará exatamente como antes.
Você pode ler mais sobre intervalos nomeados no Excel. Eu expliquei todos os aspectos do intervalo nomeado aqui.
11. Caracteres curinga para correspondência parcial usando VLOOKUP
VLOOKUP permite correspondência parcial. Sim, ao fazer a correspondência exata de VLOOKUP, você pode usar operadores curinga para fazer a correspondência parcial. Por exemplo, se você deseja pesquisar um valor que começa com ‘Gil’, você pode usar o operador curinga * com ‘Gil’ como “Gil *”. Vamos ver um exemplo.
Aqui eu quero obter notas de alunos cujo nome começa com Gil. Para isso, vou escrever a fórmula abaixo.
= PROCV ("* Gill", C2: D14,2,0)
Existem dois curingas disponíveis no Excel para uso com a função PROCV.
Caractere curinga asterisco (*). É usado quando o usuário não sabe o número de caracteres existentes e só conhece alguns caracteres correspondentes. Por exemplo, se o usuário souber um nome que começa com “Sm”, ele escreverá “Sm *”. Se o usuário souber que é um valor de pesquisa que termina com “123”, ele escreverá “* 123”. (o curinga só funciona com textos). E se ele souber que “se” vem dentro de um texto ele vai escrever “* se *”.
Ponto de interrogação ("?"). Isso é usado quando o usuário sabe o número de caracteres que faltam. Por exemplo, se eu quiser pesquisar um valor que tenha 4 caracteres, mas não sei o que são, simplesmente escreverei “????” no local do valor de pesquisa.
O Vlookup retornará o respectivo valor do primeiro valor encontrado que contém exatamente quatro caracteres.
Importante: O curinga funciona apenas com valores de texto. Converta números em texto se quiser usar curinga com eles.
As fraquezas da função VLOOKUP
VLOOKUP, de fato, é uma função fácil e poderosa para buscar valores, mas há muitas áreas em que VLOOKUP não é tão útil. Se você está trabalhando no excel, você deve conhecê-los também e como fazer tarefas que VLOOKUP não pode fazer.
12. Não é possível buscar valor à esquerda de Table_Array
A maior falha da função VLOOKUP é que ela não pode buscar o valor à esquerda da matriz da tabela. VLOOKUP pesquisa apenas à direita do valor de pesquisa.
Isso ocorre porque VLOOKUP pesquisa por determinado valor de pesquisa na primeira coluna de dado array da tabela. Ele nunca retornará um valor de fora da tabela. E se tentar manter a coluna esquerda na matriz da tabela, ela se tornará a primeira coluna da matriz da tabela, portanto, o valor de pesquisa será pesquisado nesse intervalo. O que provavelmente gerará erro.
Para pesquisar valores à esquerda de um valor de pesquisa, usamos INDEX-MATCH. A função INDEX-MATCH pode pesquisar valores de qualquer coluna da planilha. Na verdade, o intervalo de pesquisa e o intervalo do qual você deseja pesquisar os valores são totalmente independentes, o que torna a correspondência de índice altamente personalizável.
PROCV retorna # N / A mesmo quando existe um valor de pesquisa.
# N / A erro é retornado pela função VLOOKUP quando o valor de pesquisa não é encontrado. Você pode achar irritante quando existe um valor no intervalo, mas VLOOKUP retorna o erro # N / A.
14. Quando os números são formatados como texto
Isso acontece principalmente quando os números são formatados como texto. Quando você encontra usando o comando CTRL + F, o Excel o encontra, mas quando você tenta usar VLOOKUP, ele retorna # N / A. Essa tarefa é dada na maioria das entrevistas como perguntas capciosas.
Na imagem acima, você pode ver que 101 está bem ali, mas a fórmula está retornando um erro. Como você lida com isso? Bem, existem dois métodos.
1. Converter texto em número em dados
Você pode ver aquela marca verde no canto superior esquerdo da coluna rollno. Eles indicam que algo está incomum no valor da célula. Ao selecionar a célula, ela mostrará que o número está formatado como texto.
Ao clicar no ponto de exclamação (!), Ele mostrará a opção de Converter em número. Selecione todas as células e clique nesta opção. Todos os valores serão convertidos em texto.
2. Converta seu número de pesquisa em texto na fórmula
No exemplo acima, alteramos os dados originais. Você pode não querer alterar nada nos dados originais. Então, você deve fazer isso na fórmula. Para alterar o número em texto na fórmula VLOOKUP, escreva isto.
= PROCV (TEXTO (G2, "0"), B2: D14,2,0)
Aqui, a fórmula altera dinamicamente o número em texto e, em seguida, combina-o com o intervalo determinado.
15. Texto com espaços à esquerda e à direita
Alguns dados fornecidos por operadores de entrada de dados e dados da Internet não estão limpos. Eles podem ter espaços à direita ou alguns caracteres não imprimíveis. Ao fazer a pesquisa usando esses valores, você pode ver algum erro # N / A. Para evitar isso, limpe seus dados primeiro. Para remover espaços à esquerda, use a função TRIM. TRIM remove qualquer número de espaços à esquerda ou à direita do texto.
Então, eu sugiro que você adicione uma nova coluna e obtenha seus dados limpos lá. Em seguida, valor cole-o. Agora, se quiser, você pode se livrar da coluna original.
= TRIM (CLEAN (texto))
Tratamento de erros de VLOOKUP
Como você sabe, PROCV não consegue encontrar um valor, ele retorna um erro # N / A. Bem, não há problema em obter o erro # N / A, talvez você às vezes pretenda obtê-lo, como quando você deseja verificar se o valor a já existe na lista ou não. Há # N / A significa que o valor não está lá.
Mas # N / A parece feio. Que tal obter uma saída amigável, como “Id não encontrado” ou “Cliente não encontrado”. Podemos usar IFERROR com a função VLOOKUP para evitar # N / A.
Use a fórmula abaixo para interceptar o erro em PROCV.
= IFERROR (VLOOKUP (150, B2: E14,2,0), "Roll no not found")
16. VLOOKUP com VÁRIOS critérios
VLOOKUP não pode funcionar com vários critérios.Sim, você só pode ter um critério para pesquisar valores usando a fórmula VLOOKUP.
Por exemplo, se você tiver o nome e o sobrenome em duas colunas separadas.
E agora, se você quiser pesquisar um valor cujo nome é John e Sobrenome Dave, você não pode fazer isso normalmente.
Mas há uma solução alternativa para isso. Você pode criar uma coluna separada concatenando o nome e o sobrenome e, em seguida, consultar essa coluna de nome e sobrenome.
Há um Alternativa INDEX-MATCH que pode pesquisar vários critérios sem qualquer coluna de ajuda.
17. VLOOKUP recupera apenas o primeiro valor encontrado
Imagine que você tenha alguns dados na região central. Agora você deseja obter os primeiros 5 registros de dados da região central. A VLOOKUP retornará apenas o primeiro valor central em todos os cinco registros. Este é um cenário importante.
Mas isso não significa que não possamos alcançar isso. Podemos usar uma fórmula de matrizes complexas como a seguir.
= ÍNDICE ($ C $ 2: $ C $ 14, PEQUENO (SE (G2 = $ A $ 2: $ A $ 14, ROW ($ A $ 2: $ A $ 14) -ROW ($ A $ 2) +1), ROW (1: 1)))
Escreva esta fórmula e pressione CTRL + SHIFT + ENTER.
E está feito.
Eu expliquei no artigo como PROCURAR vários valores no Excel.
Então, sim pessoal, neste artigo eu cobri todos os aspectos possíveis da função VLOOKUP de acordo com meu conhecimento. Se você acha que perdi algo, por favor me avise na seção de comentários abaixo.
Os 5 principais valores do Vlookup com valores duplicados usando INDEX-MATCH no Excel
VLOOKUP Valores Múltiplos
VLOOKUP com Dynamic Col Index
Correspondência parcial com a função VLOOKUP
Use PROCV de duas ou mais tabelas de pesquisa
Vlookup por data no Excel
Usando uma fórmula VLOOKUP para verificar se existe um valor
Como PROCURAR em planilhas diferentes do Excel
PROCV com números e texto
IF, ISNA e função VLOOKUP
Função ISNA e VLOOKUP
Função IFERROR e VLOOKUP